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!

Feeling pretty dumb right about now....I was placing the formula in a calculated column instead of a measure 🙄. Works now.

Rankx Within Subcategory Not Working | Microsoft Fabric Community

7 Replies

Ashish_Mathur
Super User
6 years ago
Hi,

Replace SUM(Transactions[Amount]) with [Total amount]. I am assuming Total amount is a measure.
- byoung
  Frequent Visitor
  6 years ago
  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
    Super User
    6 years ago
    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
byoung
Frequent Visitor
6 years ago
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.
- byoung
  Frequent Visitor
  6 years ago
  Feeling pretty dumb right about now....I was placing the formula in a calculated column instead of a measure 🙄. Works now.
  - v-deddai1-msft
    Community Support
    6 years ago
    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