Create a Rank

[Anonymous]

Hi, 

I'm trying to create a ranking of the accounts count of this table. 

That table is a duplicate of another table that has the real data. I created that table using the command "grouping by" and with the operation count.

Basically, I want to know the rank of every ACCOUNT according to the number of appearances (Count) that every account has.

I created this formula, but it doesn't work, as it simply shows "1" for all accounts.

Rank =

RANKX (

ALL ( 'Tabla Account Count'[Account Name] ),

CALCULATE (

COUNTROWS ( 'Tabla Account Count' ),

ALLEXCEPT ( 'Tabla Account Count', 'Tabla Account Count'[Count] )

),

,

DESC,

DENSE

)

Thank you very much in advance!

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can use this measure

Rank Account Name = RANKX( ALL(Table3[Account Name]), CALCULATE(SUM(Table3[Count])),,DESC,Dense)

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[AntrikshSharma]

@Anonymous You are getting 1 because CALCULATE inititates Context Transition and adds the currently iterated  Account Name to the filter context and the formula becomes, 

=
CALCULATE (
    COUNTROWS ( 'Tabla Account Count' ),
    'Tabla Account Count'[Account Name] = "Something", -- Coming from context transition
    ALLEXCEPT ( 'Tabla Account Count', 'Tabla Account Count'[Count] )
)

 Since ALLEXCEPT being a CALCULATE modifier is evaluated after context transition the COUNTROWS that you get are for whole table and can only be filtered by the Count column. 

Try this code instead:

Rank =
RANKX (
    ALL ( 'Tabla Account Count'[Account Name] ),
    CALCULATE ( COUNTROWS ( 'Tabla Account Count' ) ),
    ,
    DESC,
    DENSE
)

[Anonymous]

Hi. Thanks! But I received the same number 1 for all accounts in the ranking 😞

[AntrikshSharma]

Can you share the pbix file or the diagram view of your model?

[amitchandak]

@Anonymous , why allexpect

Rank =
RANKX (
ALL ( 'Tabla Account Count'[Account Name] ),
CALCULATE (
COUNTROWS ( 'Tabla Account Count' ),
),
,
DESC,
DENSE
)

this will rank for COUNTROWS ( 'Tabla Account Count' )

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

[Anonymous]

The following error appears :

Argument '2' in CALCULATE function is required 

I don't know exactly what to put since I'm new in DAX 😕