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(           ...
Need Help
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

     

     

    • bdehning's avatar
      bdehning
      Post Prodigy
      3 years ago

      I already have a Top N in my Table to Select top 5 Cities.   

       

      I need to create a new measure that can check Count of an item first and then use the highest sum of each item to break ties.  

       

      This is what gets me the Top 5 when I use "Is Less than or greater to 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])
      )

       

      Now I need to be able to break ties by taking the highest Sum of counts that tie.   I will be using LossRunToExcel[Sum of Total Gross Incurred]

       

      Ideas?

       

       

       

       

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        3 years ago

        Hi， bdehning 

        Not sure what you mean.

        Can you share your sample data and expected result? It's easier to take a look at the problem.

        Best Regards,
        Community Support Team _ Eason