Thanks, I think I did not explain clearly about the ranking. The ranking on the profit is done within each Category, P1 and date. I want to get the bottom 13th value.
I’m still a bit confused, as the title of your initial post says you want the 13th last value, yet your latest post description and data screenshot is looking for the 3rd last value, so I had to pick one and picked the 3rd last.
As with most things in Power BI, there are many ways to do the same thing … here’s one way to identify the profit value for the third last date for a product using a number of intermediate measures:
xCurrent Date = SELECTEDVALUE( Products[Date] )
xCurrent Product = SELECTEDVALUE( Products[Product] )
xMax Date =
CALCULATE(
MAX( Products[Date] ),
FILTER(
ALLEXCEPT( Products, Products[Product] ),
MAX( Products[Product] ) = [xCurrent Product]
)
)
xMax Date Minus 2 =
IF( [xCurrent Date] = [xMax Date] - 2, [xCurrent Date], BLANK() )
3rd Last Profit Value =
IF( ISBLANK( [xMax Date Minus 2] ), BLANK(), MAX( Products[Profit] ) )
Busy day … quick-and-dirty partial solution (hopefully). I tried again using a calculated column and a measure, and this gives me the bottom 3 values (whereas I think you are looking for only the 3rd-lowest value). I got a bit stuck in trying to handle cases where there are duplicate lower values (e.g., when both the 2nd and 3rd lowest profit values are the same), but here’s my work-in-progress:
Hi @jonatong, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
This partially solve my problem. You are correct that I am looking for the 3rd lowest value. So for P1, there is no 3rd value, but 2 2nd value. Is it possible to rank it by count so that there is a 3rd lowest value? Thanks
I haven’t come across a way … I’d guess you’d need to add in an additional field to break ties so that your 2 “2nd” values could be designated as a “2nd” and a “3rd” (i.e., so they could be ranked uniquely and you’d always have a “3rd”) …
Greg
Sorry I mean that there is sub product type which might have profit for the same date for the same product which i need to rank the daily profit by the profit…
Hi @jonatong. To help visualize your issue, perhaps you could attach your work-in-progress PBIX file and an annotated screenshot and/or an Excel mockup of your desired outcome. Greg