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
Super User II
Super User II

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
Super User II
Super User II

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

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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors