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

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)

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.

1 ACCEPTED SOLUTION
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

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

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.
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!

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

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

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.
Advocate I

Lin,

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

Best regards,

Fabio

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:

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.
Advocate I

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

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

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.
Advocate I

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

## Helpful resources

Announcements

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors