Forum Discussion

bdehning's avatar
bdehning
Post Prodigy
3 years ago

Need TOPN

I have the following measure and what do I add to show only the Top 5?

 

Rank Cause Grouping =
IF (
    ISINSCOPE(InjuryCause[Cause Grouping] ),
    RANKX(
        CALCULATETABLE(
            VALUES(InjuryCause[Cause Grouping] ),
            ALLSELECTED (InjuryCause[Cause Grouping] )
        ),
        LossRunToExcel[Count of Total Gross Incurred])
)
Need Help

6 Replies

    • bdehning's avatar
      bdehning
      Post Prodigy
      3 years ago

      I got that to work but I get ties and end up with more than 5 values.    As I use [Count of Total Gross Incurred] for the first calculation, how can I add the next calculation to check the [Sum of Total Gross Incurred] to break ties to stay at 5 or less?

      • grantsamborn's avatar
        grantsamborn
        Solution Sage
        3 years ago

        TOPN can also deal with ties.

        TOPN – DAX Guide

         

         

        --  TOPN might return more than the requested rows in presence of ties.
EVALUATE
    TOPN (
        3,
        ADDCOLUMNS (
            VALUES ( 'Product'[Product Name] ),
            "@Sales Amount", MROUND ( [Sales Amount], 500000 )
        ),
        [@Sales Amount],
        DESC
    )
ORDER BY [@Sales Amount] DESC
 
--  Multiple sorting criteria can be provided in further parameters.
EVALUATE
    TOPN (
        3,
        ADDCOLUMNS (
            VALUES ( 'Product'[Product Name] ),
            "@Sales Amount", MROUND ( [Sales Amount], 500000 )
        ),
        [@Sales Amount],
        DESC,
        [Product Name],
        ASC
    )
ORDER BY [@Sales Amount] DESC