Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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"

Need Help
Show and Tell
Tutorial Requests

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Jihwan_Kim
        Super User
        3 years ago

        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.