cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luukvv93
Helper II
Helper II

Need help with ranking measure

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])

tempsnip.png

 

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

 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @Luukvv93 

To receive below result.

image.png 

 

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



View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

Hi @Luukvv93 

To receive below result.

image.png 

 

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



View solution in original post

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?

 

tempsnip.png

Mariusz
Community Champion
Community Champion

Hi @Luukvv93 

Can you share your DAX Expression?

Thanks
Mariusz

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!