Power BI: Add Category ‘Other’ to Charts

Source: Pexels.com

The Problem

Microsoft keeps improving Power BI at a faster pace. Whenever I run into shortcomings, my first idea is to wait it out and assume it will be resolved very soon by one of the new updates. However, the client waiting for the dashboard does not always share my optimism and patience and for that reason, I often need to be creative.

One of the problems I encountered a while ago is having a pie chart with too many categories; When you have a pie chart with let’s say 20 with various percentages (obviously summing up to 100%) the chart might become difficult to read. Of course, you can use Power BI’s top-N feature, but this will invariably exclude the remaining data. Meaning the 100% would consist solely out of the visible partitions, which might create confusion.

A solution for this would be to set a certain percentage as a threshold and group all the parts below the threshold together in a partition named ‘Other’.

Before and after setting the cutoff at 10%

Even better would be to add a slicer where you change the threshold dynamically. After playing around with some measures and queries, I came up with the following solution.

The result

Let me walk you through my methodology so you can try it yourself…

The Blueprint

The Data

Before getting your hands dirty, it’s important to understand your data. For this example, I used the stats from the LA Lakers 2015–2016 season, the farewell season of Kobe Bryant. For this example we will only use two columns for the chart; the names of the players and the number of points they scored over the course of a full season.

The data we will work with

Now that you understand the data, we can get to work.

Add Category ‘other’

If you want to plot a value in Power BI, there should be a row or measure for it. Assuming you do not have a row named “Other” in your data with no values, you are left with two options; creating a new row or using a measure. A measure would seem like the best choice, but this would mean you would have to combine it with the rows you already have. Consequently, adding a new row seemed the best option for me.

In Power BI you can not simply add a new row to a table you already loaded, but what you can do this to create your own table and append it. With some simple steps, you can add a new player to the stats named “Other players”. As the number of points he has will vary by the number of players it represents, we will assign this other player zero points, at least for now.

1. Manually create a new table (‘Enter data’) with the same column headers
2. Only create one single row. The table should look like this:

Create table for the extra “Other players” row.

3. Append the table to the main table containing the scores. (Edit queries/Transform data — Home — Combine — Append queries)

Ok, the data is ready. But before we move on to the measure, we need to do some more preparations.

Create a Dynamic Threshold

Attention: you can skip this step if you are not interested in dynamic percentages. If you want to use a fixed threshold (for example 10%), you can jump to the next section, “Write measure”. If you want the user to dynamically change the percentage you are going to have to follow another step:

The preparation is very simple. All you have to do is to manually create another table, that looks like this:

Create a table for the percentage slicer values.

For this example, I created values between 0 and 15%, but you can of course use whatever range and stepsize you prefer.

Note: Make sure this table is in no way connected to the other tables in your dataset. Once the table is set, you can add a slicer to your report where you can select the desired cutoff point.

Write Measure

Enough with the preparations. It’s time to write the core of the solution; the measure.
In order to make my code more readable and adjustable, I tend to use a lot of variables. Let’s start by defining some key values: The cutoff percentage we selected in the slicer and the name of the player for which we will do a calculation.

Points_calculation =
VAR percentage = MAX(Threshold[Percentage]) -- can also be fixed 
VAR current_player = MAX(‘Season total’[Name])

Note: If you do not want a dynamic percentage as mentioned earlier, you can just set the percentage variable to a fixed value between 0 and 1.

Now let’s calculate the total number of points scored by the team.

VAR total_points = CALCULATE(
    SUM(‘Season total’[PTS]),
    ALL(‘Season total’)
    )

From the information above, we can calculate a cutoff number. Let’s say the team scored 2000 points in total, and you set the percentage at 10%, the cutoff number will be 200. Any player that would have less than 200 points, should be categorized in the partition “Other players”.

VAR cutoff_number = total_points * percentage

Using this cutoff point we can now calculate the total sum of points above and below the threshold.

VAR sum_above_cutoff = SUMX(
    FILTER(
        ALL(‘Season total’),
        ‘Season total’[PTS] > cutoff_number),
    (‘Season total’[PTS])
    )
VAR remainder = total_points-sum_above_cutoff

In the last part of the code, for each player, we will now return either return his points if he passes the threshold or return nothing ( BLANK() ) if he did not. For the category “Other players” we will return the sum of those who didn’t make the cutoff ( VAR remainder).

VAR filter_points = IF(
    MAX(‘Season total’[PTS]) < cutoff_number,
    BLANK(),
    MAX(‘Season total’[PTS])
    )
VAR result = IF(
    current_player = “Other players”,
    remainder,
    filter_points)
RETURN result

And that’s all the code you need. All that is left now is to plot the result. But first, let me summarize the whole measure in one block of code:

Points_calculation =
VAR percentage = MAX(Threshold[Percentage])
VAR current_player = MAX(‘Season total’[Name])
VAR total_points = CALCULATE(
    SUM(‘Season total’[PTS]),
    ALL(‘Season total’)
    )
VAR cutoff_number = total_points * percentage
VAR sum_above_cutoff = SUMX(
    FILTER(
        ALL(‘Season total’),
        ‘Season total’[PTS] > cutoff_number),
    (‘Season total’[PTS])
    )
VAR remainder = total_points-sum_above_cutoff
VAR filter_points = IF(
    MAX(‘Season total’[PTS]) < cutoff_number,
    BLANK(),
    MAX(‘Season total’[PTS])
    )
VAR result = IF(
    current_player = “Other players”,
    remainder,
    filter_points)
RETURN result

The Plot

Plotting the results is very simple, all you have to do is to plot a pie chart (or any other visual of your choice) and set the fields like this:
- Legend: ‘Season total’[Name]
- Values: Points_calculation

The result should look like this:

Final result

Source: https://giphy.com/

Conclusion

As you can see, it is fairly simple to create a dynamic “other” partition in your visualization. However, the manual I wrote here is actually an adapted version from a more complex version I created earlier containing category selection, indicators and row aggregations.

I am very confident Microsoft will soon create a solution that won’t require a hacky solution for this along with the other shortcomings they may currently have. But as long as we discover needs that are not addressed, there is nothing left to do but to stay creative and write some more code ourselves.

Feel free to take a look at some other tools or tutorials I created to improve your Power BI dashboards or other data applications.

About me: My name is Bruno and I work as a data scientist with Dashmote, an AI technology scale-up headquartered in The Netherlands. Our goal is bridging the gap between images and data thanks to AI-based solutions.
Check out my other work on https://www.zhongtron.me.