MATRIX BY TOP 20 AND THE REST IN CATEGORY OTHER

[Aydeedglz]

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"

[Aydeedglz · Accepted solution]

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

[Jihwan_Kim]

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] )
    )

[Aydeedglz]

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

[Jihwan_Kim]

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.