Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fabiocovre
Advocate I
Advocate I

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)

 

Resultado Esperado.png

 

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?

 

Resultado Obtido.png

 

 

This is my database structure

 

Fields.png

 

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.

 

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

21.png

Result:

22.png

Here is demo, please try it

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

 

  

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Community Support
Community Support

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

21.png

Result:

22.png

Here is demo, please try it

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

 

  

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Lin,

 

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

 

Best regards,

Fabio

 

v-lili6-msft
Community Support
Community Support

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:

5.PNG6.PNG

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

3.png

 

Thank you very much for your help!

 

Regards,

Fabio

v-lili6-msft
Community Support
Community Support

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

5.png6.png

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

 

2.png

 

Regards,

Fábio

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.