Forum Discussion
RANX Function
Hi All,
this might be a very silly asking this question On RANK DAX, however i am new and learning POWER Bi. please if you could you help me.
I have a table and i want to give "Ranking" on the basis of amount that client due,
within the table there is column at the end called "Debt Overdue" which represent "Yes" and "No" if the amount is due then "Yes"
and if not "No".
"
so i trying to prepare the ranking on both the condition, if i select "Yes" then should get the ranking, if select "NO" then also should reflect the ranking. please if you could help and advise .
i tried applying this on excel using countif .
sandee Is this how you want it? I have attached the file below my signature.
Measure = VAR Yes = CALCULATETABLE ( Sandee, Sandee[Debt Overdue] = "yes", ALL ( Sandee ) ) VAR No = CALCULATETABLE ( Sandee, Sandee[Debt Overdue] = "no", ALL ( Sandee ) ) VAR CurrentSelection = SELECTEDVALUE ( Sandee[Debt Overdue] ) VAR Ranking = IF ( CurrentSelection = "yes", RANKX ( Yes, [Total Due],, DESC ), RANKX ( No, [Total Due],, DESC ) ) VAR Result = IF ( ISINSCOPE ( Sandee[Debt Overdue] ), Ranking ) RETURN Result
12 Replies
sandee - See if this gets you there, otherwise @ me and I'll take a closer look https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452
Hi Greg,
thanks for the reference,
it seems nothing is working here, i am not getting the desired output. working on one formula from almost one week, really Fed up.
- sandee does my solution work for you?
Hi Antriksh,
Super Duper Shot ........ Great ......
the file u attached this is exactly i am after, i have not applied in my file as it seems stuck, but i will get back to you on this. Many Many thanks for this. God Bless You....
- sandee Great, once you are able to replicate the same in your model and have no issues, then please mark my post as solved so that others can easily find the solution. Otherwise let me know if you still face any issue.
sandee ,
if(selectedvalue(slicer[slicer]) ="no",
rankx(all(table[Cust no]), calculate(sum(Table[amount]), filter(Table,Table[Debt Overdue] ="no"))),
rankx(all(table[Cust no]), calculate(sum(Table[amount]), filter(Table,Table[Debt Overdue] ="yes")))
)For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415Hi Amit,
Many thanks for your response, here is what i am getting after applying:
sorry for late response, this is because of poor internet connection. please can you advise further as how we can get proper ranking starting from 1 to the end of clients ?
sandee , I think this same case, what I faced
City Rank = RANKX(all(Geography[City]),[Sales]) // DO not work with City ID
Geography Rank = RANKX(all(Geography),[Sales]) // work with City ID
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales])// work with City IDTry you Rank on both Cust Id and Cust name
Rank = RANKX(all(Table[Cust ID],Geography[Cust Name]),Calculate(Sum('Working Ledger'[Amount Due])))
Basically rank is inside the other group by selected in the context
