Here is a scenario , wherein I require help, I need two columns which basically gives me 1st best and 2nd best amount in the below format (screen shot attached). So basically the 2nd best value should get replaced with current best value once current amount greater than previous amount. I believe screen shot would give a clear understanding of the requirement.
If you look at the example : The current best weight is updated with new best weight if it is great than the current best weight and we have a column previous best weight which tracks the previous best weight.
In the images you have shared, is the calculation of the Current 2nd Best Weight measure for SUBSCRIPTION_ID = A correct? I think it does not follow the same logic as for the SUBSCRIPTIN_id = B
@jafernandezpuga - Yes you are correct , it doesnt follow the same logic but i real world scenario both the subscription id would be in the same table so the logic should hand both the use case. Also if you see my screen shot of dataset 1 the 2nd best weight is not giving the correct result. Screen shot below
@jafernandezpuga - It should display the current best amount itself because it has still not come to the row where there is an amt greater than current best amt , so from row 1to 5 it should show 1107.504. now at row 6 u see an amt great than current best amt has arrived now again the 2nd best amt becomes same 1107.504 when u reach row 9 , u notice now there is an amount greater than ur current best amount so then ur current best amount becomes ur 2nd best amount. Same pattern is applicable for S2 as well , so now the i made a mistake in dataset 2 screen shot , from row 1 to until it has Amt of 270 the 2nd best Amt should 240 instead of 250 or 210
Hi @Vishy ,
If you can upload the image with the correct result from the table S2, I believe that by applying the 2nd Best Weight measure to a virtual table with the Current Best Weight column, we could obtain the desired result.
Hi @Vishy,
In this last image, to calculate the 2nd Best Weight of the row on 3/1/2021, what Total Weight values are taken into account? If we are in the first row, another value is taken into account other than the value of the current row, which is 250. And in the second row of 4/1/2021, the values of 250 and 210 (current and earlier)?
Wouldn’t the value of 250 be for the entire column?
@jafernandezpuga - I understood your point here, and current the pbix what you have shared gives the ideal result , but just from the feasibility check perspective is their any possibility that as per above image the the current best weight is 250 right , so it would display the next minimum value that is 240 for all the rows where you have 250 as the current best once there is something greater than 250 i.e 270 in above case so the 2nd best weight for those rows would be the immediate next max value which is 250 for all the rows of 270, kindly let me know whether this makes sense
Hi @Vishy, did the response provided by @jafernandezpuga help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
