Need TOPN

[bdehning]

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])

)

[amitchandak]

@bdehning , use a visual level filter, filter this for <=5

else create a TOPN -TOPN: https://youtu.be/QIVEFp-QiOk  in place of rank

[bdehning]

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]

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]

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]

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

[bdehning]

Can you close this string out.  I had to work on another string.