Forum Discussion

fabiocovre's avatar
fabiocovre
Icon for Advocate I rankAdvocate I
8 years ago
Solved

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...
  • v-lili6-msft's avatar
    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

    Result:

    Here is demo, please try it

    https://www.dropbox.com/s/aav3f2g16zb92t6/Top%20N%20and%20Others%20-%20monthly.pbix?dl=0

     

      

     

     

    Best Regards,

    Lin