Creating a table displaying each of the Top 5 responses and put remainder into 'Other' (2 slicers)

[misterlau14]

Hi,

I am currently stuck on a problem with a Power BI file that I have created  --> Testing file 

Background of file

Within the file, users select from 2 different slicers:

1. Settlement Location

2. Financial Year of Arrival

Note the following points about the table I have created in the file:

• I have currently written a measure named CC_Ranking1 that calculates the ranking of each response for the Client Citizenship variable (this is based on the number of times it occurs in the dataset and also based on the user's selections from the 2 slicers)
• Note: 'Other' and 'Not Stated' are responses that belong to a Excluded category, hence have no calculated ranking

My problem (that I would like to solve)

I would like to create a separate table with 6 rows for the variable Client Citizenship (with the same structure as seen in the Excel screenshot below)

• 1 row for each of the Top 5 responses
• The last row will be combining all responses (that have a ranking of 6 and higher and is not blank)

I can do this in Excel with no issues but currently do not understand how to implement this same table in Power BI.

I will be most grateful for any assistance - please make any required updates to my attached Power BI file --> Testing file

Thanks heaps

[misterlau14 · Accepted solution]

Update: I managed to find the solution to my problem

In case it helps anyone else who deals with something similar to my original problems - this YouTube video solved everything for me (it's 36 mins long but the narrator talks at a pretty good pace so that you can follow along) - https://youtu.be/nVvlEHKr_0o?si=WLSoFLSFE5qZgMYV 

[v-ssriganesh]

Hello @misterlau14,
We appreciate your effort and are glad to hear that your issue has been resolved. Your update will also help other community members address similar issues more efficiently.

[donbuser]

Hi — this is a classic Top N + “Other” scenario, and you’re actually very close already since you’ve built the ranking measure 👍

The missing piece is creating a grouping logic that separates Top 5 vs everything else, while still respecting slicers.

Step 1: Create a grouping measure (Top 5 vs Other)

You can build a measure like this:

Citizenship Group =
VAR RankVal = [CC_Ranking1]
RETURN
IF(
RankVal <= 5,
SELECTEDVALUE('Table'[Client Citizenship]),
"Other"
)
Step 2: Create a measure for counts

Now calculate your values using this grouping:

Count Grouped =
CALCULATE(
COUNTROWS('Table'),
ALLEXCEPT('Table', 'Table'[Client Citizenship])
)

(Adjust this depending on how you're currently counting occurrences — reuse your existing measure if you already have one.)

Step 3: Use in a Table Visual
Rows → Citizenship Group
Values → your count measure

This will:

Show Top 5 individually
Automatically group everything else into “Other”
Still respect both slicers (Settlement Location + Financial Year)
⚠️ Important Note

Measures alone can sometimes cause duplicate “Other” rows depending on context.

If that happens, a more robust approach is to:

Create a disconnected table with values: Top 1–5 + “Other”
Then map your ranking into that table using measures

But in many cases, the measure-based approach above works fine.

💡 Big Picture

What you’re doing here is essentially recreating what Excel does easily with grouping — but in Power BI, you need to explicitly define that logic through measures or model structure.

Once set up, though, it becomes dynamic and slicer-aware, which is where Power BI really shines.

[misterlau14]

Hi there @donbuser ,

Thanks very much for the helpful tips in your reply.

I followed Steps 1, 2 and 3 as you mentioned.

I now have a table visual that displays each of the top 5 ranked responses (which is perfect).

Unfortunately, I do now have duplicate rows of 'Other' appearing in the table visual. I do see the points you mentioned about needing to create a disconnected table to get around this issue. 

I think my brain is just stomped at the moment - can I please ask a big favour of you (which is to open the file that I have linked below and implement the updates you suggested)?

https://drive.google.com/file/d/1FpWc84iaez_pvX3Uh4lrapVO8MnDWvDj/view?usp=sharing

Thanks

[danextian]

Please make your file public.

[misterlau14]

Hi,
I have saved the testing file to my Google Drive (you should be able to access it via the link below)
https://drive.google.com/file/d/1pHmk2eaIdew3YSh7gLPXjbWizthPNOdM/view?usp=sharing 

Thanks

[danextian]

The link requires a  permission

[misterlau14]

Apologies about that, I have updated the settings in Google Drive so that anyone with the link to the file can now view.
https://drive.google.com/file/d/1pHmk2eaIdew3YSh7gLPXjbWizthPNOdM/view?usp=sharing

I hope you can access the file now, please let me know if you still can't.

Thanks

[pankajnamekar25]

Hello @misterlau14 

Create a small TopN table for slicer

TopN_Selection = DATATABLE("TopN", INTEGER, {{3},{5},{10}})

Capture selection
Selected_TopN = SELECTEDVALUE(TopN_Selection[TopN], 5)

Ranking
CC_Ranking1 =
RANKX(
ALLSELECTED('Table'[Client Citizenship]),
[Total_Count],
,
DESC
)

Grouping
Citizenship_Group =
IF(
[CC_Ranking1] <= [Selected_TopN],
SELECTEDVALUE('Table'[Client Citizenship]),
"Other"
)

Final_Count =
VAR TopN = [Selected_TopN]
RETURN
IF(
[CC_Ranking1] <= TopN,
[Total_Count],
CALCULATE(
[Total_Count],
FILTER(
ALL('Table'[Client Citizenship]),
[CC_Ranking1] > TopN
)
)
)

Use Citizenship_Group in rows and Final_Count in values you’ll get dynamic Top N + one “Other” row working with both slicers.

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.

Thanks,

Connect with me on:
LinkedIn |
Data With Pankaj - YouTube