cancel
Showing results 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

Helper V

## MATRIX BY TOP 20 AND THE REST IN CATEGORY OTHER

Hi,

I am trying to bulid a matrix where I will see the top 20 costumers, but I still want to see the others as "Other" So I will have 21 rows in the matrix, any ideas how?

Example: I have 200 costumers I want to see the top 20 and the other 180 in a row named "Other"

1 ACCEPTED SOLUTION
Helper V

Hi, I have already solved it, the next articules have STEP BY STEP and EXAMPLE FILE, if someone need it

https://www.proserveit.com/blog/ms-power-bi-topn-and-other

Also, this one is good but it didn't worked for me:

https://www.oraylis.de/blog/2016/show-top-n-and-rest-in-power-bi

4 REPLIES 4
Helper V

Hi, I have already solved it, the next articules have STEP BY STEP and EXAMPLE FILE, if someone need it

https://www.proserveit.com/blog/ms-power-bi-topn-and-other

Also, this one is good but it didn't worked for me:

https://www.oraylis.de/blog/2016/show-top-n-and-rest-in-power-bi

Super User

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

``````Top 20 and others measure: =
VAR _top20list =
CALCULATE (
SUM ( Sales[Sales] ),
KEEPFILTERS (
TOPN (
20,
ALL ( Customer[Customer], Customer[Index] ),
CALCULATE ( SUM ( Sales[Sales] ) ), DESC
)
)
)
VAR _top20salestotal =
CALCULATE (
SUM ( Sales[Sales] ),
TOPN (
20,
ALL ( Customer[Customer], Customer[Index] ),
CALCULATE ( SUM ( Sales[Sales] ) ), DESC
)
)
VAR _allsalestotal =
CALCULATE ( SUM ( Sales[Sales] ), REMOVEFILTERS ( Customer ) )
RETURN
IF (
HASONEVALUE ( Customer[Customer] ),
SWITCH (
SELECTEDVALUE ( Customer[Customer] ),
"Others", _allsalestotal - _top20salestotal,
_top20list
),
SUM ( Sales[Sales] )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper V

Hi, I have a problem

The customer can be repetaed multiple times in my sales table so it crates a many to many relationship. I still made it and the table dosen't give me the top 20

Super User

Hi,

If the Dimension-Customer-table does not have repeating customers, I think it can have one to many relationship.

If it is OK with you, please share your sample pbix file's link ( onedrive, googledrive, dropbox, or any other links) here, and then I can try to look into it to come up with a more accurate solution.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors