Displaying each of Top 5 responses and group the rest into 'all other responses'

[misterlau14]

Hi everyone, I would like to receive some assistance please.

Please click on the link below to access my current PBI working file

Occupation data 

Background

I received help with creating a new measure (see DAX code below) that calculates the rank of each reported response (with some specific responses being excluded) based on the number of times that it appears in the data. 

Rank_Occupation_v2 =

RANK(DENSE,

    FILTER(ALL('ALL Arrivals data'[Occupation]),

    'ALL Arrivals data'[Occupation]<>"Not stated"&&'ALL Arrivals data'[Occupation]<>"Not previously employed"&&'ALL Arrivals data'[Occupation]<>"Non Working Child"&&'ALL Arrivals data'[Occupation]<>"Occupation Unknown"),

    orderby([Count_Occupation_],DESC))

Using the code above, I have created a table in the attached PBI working file which provides me with the output I initially wanted.

New output required

Please refer to the screenshot in the link attached below (this is from Excel - I am trying to see if I can replicate this same output in Power BI)

Ideal output (pie chart and table) - Occupation data 

I would now like to create a table that displays the following for each of the top 5 ranked responses:

• Reported response
• Number of responses

Additionally, I would like to have a 6th (and final) row in the table (below each of the top 5) that is for 'All other responses' and also displays the combined number of responses (for all responses that are ranked from 6 and below).

Seeking your assistance

I'm still a newbie with using Power BI and so I will be most grateful for any assistance that anyone can provide.
Not sure if I should be creating a new measure (or a new column or a combination of both) to create my new output that is now required.

Many thanks

[Anonymous · Accepted solution]

Hi @misterlau14 ,

Please check whether this is what you want:
First I use this DAX to create a calculated table:

TopOccupationsTable = 
UNION(
    ADDCOLUMNS(
        FILTER(
            VALUES('ALL Arrivals data'[Occupation]), 
            [Rank_Occupation_v2] <= 5 && [Rank_Occupation_v2] <> BLANK()
        ),
        "Rank", [Rank_Occupation_v2]
    ),
    ROW("Occupation", "All other responses", "Rank", "other")
)

Then create the Table visual with this calculated table and use this DAX to create a measure:

Count = 
IF(
    MAX('TopOccupationsTable'[Occupation]) = "All other responses",
    CALCULATE(
        COUNT('ALL Arrivals data'[Occupation]),
        FILTER(
            ALL('ALL Arrivals data'[Occupation]),
            [Rank_Occupation_v2] > 5
        )
    ),
    CALCULATE(
        COUNT('ALL Arrivals data'[Occupation]),
        'ALL Arrivals data'[Occupation] = MAX('TopOccupationsTable'[Occupation])
    )
)

Also put the measure into the Table visual and the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[misterlau14]

Thanks heaps for your help, very much appreciated 🙂

[Ashish_Mathur]

Hi,

I have answered a similar question in the attached files.  Please study these example files and apply these measures to your dataset.

Hope this helps.

[misterlau14]

Thank you for attaching those two sample PBI files in your reply, they did assist me with obtaining the output I was looking for

[Ashish_Mathur]

You are welcome.