Power BI Blog: Dealing with an Excessive Number of Categories

Welcome back to this week’s edition of the Power BI blog series. This week, we will look at grouping categories when the number becomes excessive.

Last week we created the following chart, where we coloured in the different categories of products on a column chart:

Looking at this chart, it shows that a large proportion of products sold come from Bikes. However, looking deeper into our data, we find that the Bikes category can actually be broken down further:

This is done by using the Subcategory Name field as the legend. We can now see the growth in different Bike types over time. Upon closer inspection, it looks like there are a lot of other subcategories that are unable to be visualised in a quantifiable manner when compared to Bike sales.

We can use the grouping functionality in Power BI to group these categories together. You can read more about grouping here. To group the data, we can go to the Data view of the report. Then, with the ProductList table selected, navigate to the ‘Table tools’ tab on the Ribbon and select the ‘Data groups’ option:

We can then assign each type of Bike as its own category and group ‘all ungrouped values’ as Other:

If we plot the Subcategory Name (Alt) field as the legend, we get the following result:

To differentiate the current year’s columns, we can repeat the process of bringing in category column into the Sales table (you can read more about this in a previous blog here).

We then create the conditional column that assigns an ‘F’ to the current year’s forecast data:

If we plot the Product Subcategory column into the visualisation we get the following result:

This colour scheme would be appropriate if this were for a circus. Let’s change the colour scheme.

That’s it for this week! A more involved process to group categories together, while retaining column differentiation. Join us next week for more on Power BI.

In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.