cancel
Showing results for
Did you mean:
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])```

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

1 ACCEPTED SOLUTION
Super User II

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.

3 REPLIES 3
Super User II

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.

Helper II

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?

Super User II

Hi @Luukvv93

Can you share your DAX Expression?

Thanks
Mariusz

Announcements

#### 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.

#### Microsoft named a Leader in The Forrester Wave

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

#### 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 Solution Authors
Top Kudoed Authors