Forum Discussion

AABright's avatar
AABright
Frequent Visitor
2 years ago
Solved

How to create a dynamic decile ranking

I am attempting to create a decile ranking which will change depending on selections. I have a Dim table like this: Dim Table     ID Group Name Number AV1 A V 1 AW2 A W 2 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    AABright So, probably something like this. PBIX is attached below signature.

    Rank2 = RANK( SKIP, ALLSELECTED('Table2'), ORDERBY( CALCULATE(SUM('Table2'[Score])), DESC, CALCULATE(MAX('Table2'[Member])), DESC) )

    or

    Rank3 = 
    VAR __ID = MAX('Table2'[Member])
    VAR __Count = COUNTROWS( ALLSELECTED('Table2') )
    VAR __Text = CONCATENATEX( ALLSELECTED('Table2'), [Member] & "^" & [Score], "|", [Score], DESC, [Member], DESC)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS( 
                GENERATESERIES( 1, __Count, 1 ),
                "__Value", SUBSTITUTE( PATHITEM( __Text, [Value] ), "^", "|" )
            ),
            "__ID", PATHITEM( [__Value], 1 ),
            "__Score", PATHITEM( [__Value], 2 )
        )
    VAR __Result = MAXX( FILTER( __Table, [__ID] = __ID ), [Value] )
RETURN
    __Result

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

AABright Well, the first issue you are going to have is that neither Dense nor Skip will provide you with a truly unique Rank. Both will assign the same number to the duplicate values. So, to get around this you have to get a bit fancy and use what is effectively known as the Mythical DAX Index: The Mythical DAX Index Quick Measure - Microsoft Fabric Community

 

Here is an example that will return a unique rank for each ID. Note that this index is generated by first sorting by score and then by ID so AX2 will be sorted above AY2 even though they have the same score. I am assuming that you can generate the rest of the DAX for your decile.

 

Rank = 
    VAR __ID = MAX('Table'[ID])
    VAR __Count = COUNTROWS( ALLSELECTED('Table') )
    VAR __Text = CONCATENATEX( ALLSELECTED('Table'), [ID] & "^" & [Score], "|", [Score], ASC, [ID], DESC)
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS( 
                GENERATESERIES( 1, 4, 1 ),
                "__Value", SUBSTITUTE( PATHITEM( __Text, [Value] ), "^", "|" )
            ),
            "__ID", PATHITEM( [__Value], 1 ),
            "__Score", PATHITEM( [__Value], 2 )
        )
    VAR __Result = MAXX( FILTER( __Table, [__ID] = __ID ), [Value] )
RETURN
    __Result

Now, the above is verbose but it is clear what is going on and how the calculation actually works. Otherwise, if you enjoy black box magical DAX then this will also return a unique ranking with the same sorting:

Rank 2 = RANK( SKIP, ALLSELECTED('Table'), ORDERBY( CALCULATE(SUM('Table'[Score])), ASC, CALCULATE(MAX('Table'[ID])), DESC) )

Both of these are measures FYI.