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

    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

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi,@fabiocovre

After my research , you can do these follow my steps like below:

STEP1:

Add rank measure

Specialtie Rank 2 = if(HASONEFILTER('Table'[MonthYear]),RANKX(ALL(Specialties[Specialties]), CALCULATE(SUM(Example[Claim]))),RANKX(ALL(Specialties[Specialties]), CALCULATE(SUM(Example[Claim]),ALLEXCEPT(Example,Specialties[Specialties]))))

then 

Total with Others = 
if([Specialtie Rank 2] <= 5, CALCULATE(SUM(Example[Claim])),
	if(HASONEVALUE('Specialties'[Specialties]),
		if(values('Specialties'[Specialties]) = "Others",
		sumx(filter(all('Specialties'[Specialties]), [Specialtie Rank 2] > 5), CALCULATE(SUM(Example[Claim]))
		)
	)
))

STEP 2:

The slicer should be from the third table

Table = VALUES(Example[MonthYear])

Result:

Here is my DEMO,please try it 

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

 

Best Regards,

Lin

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

    Hi Lin,

     

    It works when I filter only one month, but when I try to filter more than 1 month, it doesn't work... As you can see in the picture below, Urologia should be 5th and Obstetricia the 6th.

     

     

    Thank you very much for your help!

     

    Regards,

    Fabio