Forum Discussion

2019's avatar
2019
Helper II
2 years ago
Solved

Show 3 highest values based on condition

I have a big table in Power BI where I want to write a DAX Expression to create a new table for each location only to show the 3 highest “losses” values.

 

But sometimes some rows have the same “losses” values therefore I want to show the rows even if they have the same number of “losses” values.

 

For example, if I filter by “location06” and select the highest 3 numbers:

 

ID

Location

Losses

16743

Location06

300

16744

Location06

300

16742

Location06

255

16753

Location06

150

 

But in case the Losses number is repeated then this is what I want to see:

ID

Location

Losses

16743

Location06

300

16744

Location06

300

16742

Location06

255

 

Link to sample datesource file Excel sheet 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Try these measures

    L = SUM(Data[Losses])
    Measure = RANK(DENSE,ALL(Data[ID]),orderby([L],DESC,Data[ID],ASC))

    Expand the filter pane and select <=3 as the filter criteria.  Hope this helps.

     

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Try these measures

    L = SUM(Data[Losses])
    Measure = RANK(DENSE,ALL(Data[ID]),orderby([L],DESC,Data[ID],ASC))

    Expand the filter pane and select <=3 as the filter criteria.  Hope this helps.