Forum Discussion
Advocate IIRANKX with alternate sort
Hi Power BI Gurus,
I have a transaction-level fact table linked to a dim_date table.
I'd like to show an aggregate of income by month, and also a rank of the months by income. However, I'd like to have the table ordered by calendar month.
I can easily achieve the ranking, or the sorting - but I can't get them to work together. Eg this Rankx function works:
Rank Value =
RANKX ( ALL ( 'Entry Date'[Period Name] ), [MyVal] )
To give:
However, I want to show the table ranked by month in chronological order. If I try to apply an alternate sort in the Data tab
this works, but ruins the rank:
Can anyone advise how I can get what I need?
thanks
Matt
4 Replies
mfinlayI can upload the test file I've used, if someone can explain how to embed a .pbix to this thread.
thanks
Matt
- mfinlay
Hopefully this is a link to the file in dropbox:
https://www.dropbox.com/s/4mif90bhdbmg5ji/RANKX%20issue.pbix?dl=0
thanks
Matt
- amitchandak
Super User
mfinlay , Use both of them. Like
Rank = rankx(ALL('Date'[Month Year Sort],'Date'[Month Year]),CALCULATE(sum('Order'[Qty])),,DESC,Dense)Main column and sort column. Tested on my data.
- mfinlay
That works perfectly in my test file, thank you.
