Rankx Within Subcategory Not Working

[byoung]

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! 

[byoung · Accepted solution]

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

[byoung]

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 · Accepted solution]

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

[v-deddai1-msft · Accepted solution]

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

[Ashish_Mathur]

Hi,

Replace SUM(Transactions[Amount]) with [Total amount].  I am assuming Total amount is a measure.

[byoung]

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.

[Vera_33]

Hi @byoung 

Do you want to rank the account in each Type respectively? Can you try this one? If not working, remove the column [Account Number], have a try, let me know how it goes.

RANKX(ALLSELECTED(Transactions[Account Desc),[Total Amount],,DESC,Dense)

[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/3...