Forum Discussion

fabiocovre's avatar
fabiocovre
Advocate 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 filtered)

 

 

Based on this post (https://community.powerbi.com/t5/Desktop/Show-Top-N-and-sum-up-the-Others-as-a-value/m-p/313063#M138849), I'm almost there. I've got this chart below, where it's showing different top 5 specialties for each month, and not the top 5 of all the period. Can you help me?

 

 

 

This is my database structure

 

 

And these are my measures:

 

Specialties = UNION(Values(Example[Specialties]); ROW("Specialties"; "Others"))
Specialtie Rank = RANKX(ALL('Specialties'[Specialties]); CALCULATE(SUM(Example[Claim])))
Total with Others = 
if([Specialtie Rank] <= 5; CALCULATE(SUM(Example[Claim]));
	if(HASONEVALUE('Specialties'[Specialties]); 
		if(values('Specialties'[Specialties]) = "Others"; 
		sumx(filter(all('Specialties'[Specialties]); [Specialtie Rank] > 5); CALCULATE(SUM(Example[Claim]))
		)
	)
))

Thank you in advance.

 

 

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

7 Replies

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

    • fabiocovre's avatar
      fabiocovre
      Advocate I
      8 years ago

      Lin,

       

      You are amazing. Thank you very much for that!!!

       

      Best regards,

      Fabio

       

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    8 years ago

    Hi@ fabiocovre

    You can try to use ALLEXCEPT function  in the measure:

    Specialtie Rank = RANKX(ALL(Specialties[Specialties]), CALCULATE(SUM(Example[Claim]),ALLEXCEPT(Example,Specialties[Specialties])))

    Basic data

    Top 5 is :F,E,D,C,B and others

    Now it shows the top 5 of all the period in the column chart

     

     

     

    Best Regards,

    Lin

    • fabiocovre's avatar
      fabiocovre
      Advocate I
      8 years ago

      Hi Lin, thank you for your answer. It's almost there...

       

      It worked, but when I filter a specific month, the ranking remain the same. The top 5 should change to Cir Toracica, Neurologia, Ortopedia, Cir Ap. Digestivo and Cir Cardiaca. Do you have any idea what can I do?

       

       

      Regards,

      Fábio

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