Forum Discussion

KMont's avatar
KMont
Frequent Visitor
3 years ago

Ranking without ties

Hi,   I am learning DAX and am struggling with an issue ranking a table to get a Top N result.  I have many ties as the data set is not large.  The table is already filtered using the filtering in ...
KMont's avatar
KMont
Frequent Visitor
3 years ago

I just want the abbreviation column and the Rx Total column.  I am wanting a top N...the first 15 will do sorted descending.  The issue is that the data set is small, and there are Abbreviations where the number ties... the 3 below all have 4 records for example so when there is a tie, I would like to rank alphabetically.

 

mup

 

 

 

tre

 

 

 

tri

   

 

The base table ‘Rx Counts’ is a big table with 204,616 rows.  It includes a unique id on each row with the abbreviation, network, month, year, and complete date.  The visual I have summarizes the count of records by abbreviation, but only includes a specific network.

 

Here is an example of ‘Rx Counts’

 

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
3 years ago

KMont thanks for that!

I take it that Rx Total is a measure.

 

I would suggest a measure like this, using the RANK function:

 

Rank = 
RANK (
    DENSE,
    -- Abbreviations values in overall context of visual
    CALCULATETABLE ( VALUES ( 'Rx Counts'[Abbreviation] ),  ALLSELECTED () ),
    -- Order by [Rx Total] and break ties with Abbreviation
    ORDERBY ( [Rx Total], DESC, 'Rx Counts'[Abbreviation], ASC ) 
)

 

Then apply a visual level filter (such as <= 15) on the Rank measure.

 

Does this work at your end? Please post back if needed.

 

Regards