Top N and Others - monthly

[fabiocovre]

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#M138...), 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.

[Anonymous]

Hi,

there's also a blog post that might help (check for 1b)

https://livingandcoding.com/blog/top-n-others-power-bi/

Regards!

[v-lili6-msft · Accepted solution]

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]

Lin,

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

Best regards,

Fabio

[v-lili6-msft]

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]

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

[v-lili6-msft]

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]

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