Need help with ranking measure

[Luukvv93]

Hello community,

I have 3 measures from which I only want to show the top 3 values and the bottom 3 values in the same column for the respective measure.

S-ratio = MAX(DataUnpivot[Solvabiliteits-ratio])

Debt/EBITDA = MAX(DataUnpivot[Debt/EBITDA-ratio])

DSCR = MAX(DataUnpivot[DSCR column])

Could you explain me whether to use TOPN or RANKX and if possible send me some code I could realise this.

Your help is much appreciated

[Mariusz · Accepted solution]

Hi @Luukvv93 

To receive below result.

 

You can modify below DAX expression. 

Rank Top and Bottom Three Sales by Brand =
VAR __grain =
    CALCULATETABLE (
        VALUES ( 'Product'[Brand] ),
        --Rank by Column 
        ALLSELECTED ()
    ) -- replace [Sales] with your Measure
VAR __top =
    RANKX ( __grain, [Sales],, DESC ) <= 3
VAR __bottom =
    RANKX ( __grain, [Sales],, ASC ) <= 3
RETURN
    SWITCH ( TRUE (), __top, [Sales], __bottom, [Sales] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Luukvv93]

Hi @Mariusz 

I tried your solution and it seems to work, great.

One question regarding the bottom 3 values:

The measure only shows the top 3 ranks (as expected) but only the 2 bottom ranks.

Any clue on how to show the 3 bottom ranks?

[Mariusz]

Hi @Luukvv93 

Can you share your DAX Expression?

Thanks
Mariusz