How do I calculate a measure to return the top 5 in sorted order?

[mohassan99]

I have a table including "MoneyLeftOnTable" and "cgToMaxPotential".  I want to sort on "MoneyLeftOnTable" DESC then "cgToMaxPotential" ASC and return the top 5 rows ordered by "MoneyLeftOnTable" DESC then "cgToMaxPotential" ASC. 

I got the top 5 by creating a rank variable to take sort by when I use TopN filter by rank variable. Now I would only like them ordered and this is not the default behavior of the TOPN filter. Can I calculate a measure to sort the results and return the top 5 so I can bypass the Top5 filter?

There is a hidden filter on the page to filter for a single provider at a single location in a single year that should result in each measure having one row in results.

I am using desktop and data import mode, not DirectQuery.

I am a novice to DAX and it seems complicated. So much nesting of things that even reading the documentation doesn't really clarify to me.

Table:

Filter:

[v-yalanwu-msft]

Hi, @mohassan99 ;

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mohassan99]

Not solved. 

[mohassan99]

@v-yalanwu-msft @amitchandak  Why am I getting an error for this:

[Ashish_Mathur]

Remove the ) from before the first comma.

[v-yalanwu-msft]

Hi, @mohassan99 ;

Can you show your current visual and expected result with some screenshot or pictures (after removing sensitive info)? I don't understand what the visual expected to be like. Dummy data is also appreciated. Thanks.

and may be rank() or topn is suit for you.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mohassan99]

There's only one value for each of these and only one "date" value stored as text for each year, "2020" & "2021". 

[amitchandak]

@mohassan99 , Assume you have measure return or last year revenue

Top 5 Rank = CALCULATE([last year revenue],TOPN(5,all(Table[Company]),[last year revenue],DESC),VALUES(Table[Company]))

for last year refer TI option

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))