Forum Discussion
MATRIX BY TOP 20 AND THE REST IN CATEGORY OTHER
Hi, I have already solved it, the next articules have STEP BY STEP and EXAMPLE FILE, if someone need it
I used this article:
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
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] )
)
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
Hi,
Thank you for your message.
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.