Displaying Top 10 AND "All Other"

[Anonymous]

What is the easiest way to bring forward top 10 and also show everything else in an "Other" category?   Example, I have a list of zip codes.   I want to display the top 10 zip codes of responders; I then want to group together not top 10 into "Other" category as one line item to show the total count and total % on one line.

[v-piga-msft]

Hi @Anonymous,

I have made a sample to reproduce your scenario.

You could create the below measures to get the output.

total = SUM(Table1[Value])

Rnak =
VAR rank_ =
    RANKX ( ALL ( Table1 ), [total],, DESC )
RETURN
    IF ( rank_ > 10, "others", rank_ )

zip =
VAR zip =
    CALCULATE ( SELECTEDVALUE ( Table1[Zip codes] ) )
RETURN
    IF ( [Rnak] > 10, "others", zip )

You could modify the measures based on your scenario, if you still need help, please share your desired output so that we can help further on it.

Best  Regards,

Cherry

[Anonymous]

I tried using the information below, but couldn't figure it out.   Here is what I am working with.   Please note this table is the result of survey responses.   I limited the table with question id "5783" which is the zip code information.   I eliminated the other survey questions and answers to make it easier to work with.   

H2 POC PBI

H2 POC.xls

[v-piga-msft]

Hi @Anonymous,

If I understand your requirement correctly that you want to rank by the count to show the top 10 zip codes and show everything else in an "Other".

If it is, please try this measure below.

Measure =
VAR rank_ =
    RANKX (
        ALLSELECTED ( 'page' ),
        CALCULATE ( COUNT ( 'page'[Survey Answer Text] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( rank_ > 10, "other", rank_ )

If you still need help, please share your desired output so that I can understand your requirement better.

Best  Regards,

Cherry

[Anonymous]

This is much closer.   I made a few changes to get what I need, however, how can the category "other" roll up into one line item to show one total count and total %.   e.g, 

Count      Measure     Zip         %

   200              1          Other     25%

In other words, 200 entries were identified as unique, they fall within the measure of "1" because there are no duplicates, they fall under the Zip category as "Other because they are a conglomerate of different zip codes, but all total, they make up 25% of the total count of zip codes.   

I've attached the latest file for review.   H2 POC

Thanks for your help.

[Anonymous]

In a nutshell, here is the desired outcome I am looking to create.    All rows that meet the 'Other' criteria are summed onto one line.   A '% Ttl' and 'Count' are created based on the calculation of 'Other'.

[v-piga-msft]

Hi @Anonymous,

Please refer to my attached simple pbix which has reproduced your scenario.

Best Regards,

Cherry

[Anonymous]

Thanks Cherry,  I have tried using the formulas you provided FROM the "mock-up" example.   I spent a lot of time integrating that into my actual PBIX.   I now have uploaded the actual table; would you mind showing me how to apply the formulas to the actual file.    The actual XLS file is a larger file; which is why I created a small mockup file.   And the design of the PBIX is a little different.

H2_POC_V3.XLS

H2_POC_V3a.PBIX

Thanks in advance.