Top N and Others - monthly

Hi, I want to create the graph below, where it will show the top 5 specialties by cost in the period (I did it manually, by grouping, but these 5 specialties should change, depending the date fil...

Need Help

v-lili6-msft

8 years ago

Hi@ fabiocovre

You can use this measure as below:

Specialtie Rank 2 = 
VAR allMonthYear =
    CALCULATE ( COUNT ( 'Table'[MonthYear] ), ALL ( 'Table' ), ALL ( Example ) )
VAR selectedMonthYear =
    CALCULATE ( COUNT ( 'Table'[MonthYear] ), ALL ( Example[MonthYear] ) )
RETURN
    IF (
        HASONEVALUE ( 'Table'[MonthYear] ),
        RANKX ( ALL ( Specialties[Specialties] ), CALCULATE ( SUM ( Example[Claim] ) ) ),
        IF (
            selectedMonthYear > 1
                && selectedMonthYear < allMonthYear,
            RANKX (
                ALLSELECTED ( Specialties[Specialties] ),
                CALCULATE ( SUM ( Example[Claim] ), ALL ( 'Example'[MonthYear] ) )
            ),
            IF (
                selectedMonthYear = allMonthYear,
                RANKX (
                    ALL ( Specialties[Specialties] ),
                    CALCULATE (
                        SUM ( Example[Claim] ),
                        ALLEXCEPT ( Example, Specialties[Specialties] )
                    )
                ),
                RANKX (
                    ALL ( Specialties[Specialties] ),
                    CALCULATE (
                        SUM ( Example[Claim] ),
                        ALLEXCEPT ( Example, Specialties[Specialties] )
                    )
                )
            )
        )
)

And when creating a relationship between Example and Table , setting cross filter direction: Single