Forum Discussion

byoung's avatar
byoung
Frequent Visitor
6 years ago
Solved

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 c...
Need Help
  • byoung's avatar
    byoung
    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's avatar
    v-deddai1-msft
    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

byoung's avatar
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.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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)