Forum Discussion
Rankx Within Subcategory Not Working
In the attached, I'm trying to rank each "Account Desc" within each account type, but the rankx formula isn't working as expected. I would exted to see, within the assets account (misspelled in the capture), a ranking from 1-9 based on the Total Amount.
Please help!
7 Replies
- Ashish_Mathur
Super User
Hi,
Replace SUM(Transactions[Amount]) with [Total amount]. I am assuming Total amount is a measure.
Hi Ashish,
Yes, Total Amount is a measure, the formula is sum(Transactions[Amount]).
I've implemented the suggested solution, but it appears the data may not be aggregating/grouping properly. The field Account Type is from a joined table, I'm not sure if that makes a difference or not, there is a 1 to many relationship.
Please see attached, thank you.
- amitchandak
byoung , refer subcategory rank in these document
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/367415
Thank you all for the advice. The issue persists, but I did notice that the rankings seem to be thrown off by negative numbers during the debugging process outlined in the articles shared by amitchandak . Please reference the concatenatex column, which shows all of the values being evaluated.
Any idea why this may be happening? Every account has positive/negative numbers that need to be summed together and then ranked. 
Feeling pretty dumb right about now....I was placing the formula in a calculated column instead of a measure 🙄. Works now.
v-deddai1-msftCommunity Support
Hi byoung ,
If you want to use measure,please refer to
measure = RANKX(ALL(Transactions[Account Desc]),CALCULATE(SUM(Transactions[Amount])),,DESC,Dense)If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
