After working in Business Intelligence for several years I have come to the conclusion that there are some visuals that are foundational to data exploration and decision making. One of the most influential of these visuals are Small-Multiple charts. Today I will be going over a solution for one found natively in Power BI.
What are small multiple charts?
A small multiple (sometimes called trellis chart, lattice chart, grid chart, or panel chart) is a series of similar graphs or charts using the same scale and axes, allowing them to be easily compared. It uses multiple views to show different partitions of a dataset. The term was popularized by Edward Tufte. - Wikipedia
Now that we know what a small-multiples chart is, lets try making one in Power BI!
The Red Herring
The tricky thing with Power BI is that the product has a lot of functionality and at the speed of its development, you might miss some of the nuances offered by the product, as there are often multiple ways to solve a problem.
So make sure to watch those monthly updates!
For this Small Multiples chart, I would like to see my dimensions Segment, Region and Category in the same view, all by Profit. The goal here is to find overall trends and relationships between these dimensions through the Profit measure.
In the case of Power BI, if you are looking to build out a small-multiple bar chart, you might immediately select either the “stacked bar chart” or the “clustered bar chart” visuals as seen below.
You will notice that when you pull in all of these dimensions to the Axis slot of a “stacked bar chart” or “clustered bar chart”, that you are only able to see a single dimension by the one value, which doesn’t necessarily address our problem.
To remedy this within this visual, know that you can drill up and down the axis by selecting the next level of the hierarchy.
Further, you can also see every possible outcome by selecting the “Lowest Level of data” icon to the left of the hierarchy drill down.
The issue here is the content can become very congested and be difficult to make inferences on. The text labels to the left can become more complex as you add more dimensions. “Technology Consumer West” is quite the mouthful!
The Real Solution
As such, the “stacked bar chart” or the “clustered bar chart” are great visuals but they don’t solve our specific use case.
I want to see profit by category, segment and region to find trends in my data.
The more effect visuals that can be used here for my questions are instead the “Table” and “Matrix” visuals.
The secret to both “Table” and “Matrix” visuals, is that they have “Conditional formatting” options available to them under the “Format” tab.
So let’s build!
- Toggle the Data bars to “On”.
- Go into the advanced controls and check the “Show bar only” and press OK. This should eliminate any text that could clutter your insights.
- Next go into your Subtotals and turn off Row and Column sub-totals to make the visual a bit cleaner.
- Realign Segment and Category, to rows and Region to column. Finally, select the “expand all down one level of the hierarchy” to get a nested view of your data. This allows you to have nested text which removes non-data ink.
- You now have a Small Multiples Bar Chart, and you can see your values by hovering!
Small Multiples Bar Chart Example 1
Small Multiples Bar Chart Example 2
Small Multiples Bar Chart Example 3
It looks as though we are losing money in the central region in our Furniture offerings for both our Home Office and Consumer Segment!
This visual leads to asking more questions of our data then previously known, moving into more questions and investigations.
Clearly, Small-Multiples can be a force for real analysis in Power BI.
You will need to first format the size of your Column headers, as they don’t dynamically change like some other visuals. So, make sure that you format them to begin with.
You can only sort by totals with the Matrix Visual, so remember that for function vs. form.
Tooltips behave a little differently in Matrix and Table visuals and are a bit minimal. I am sure, the Power BI will address this in a future build.
This technique applies to Horizontal Bar - Small Multiples Charts, but perhaps the Power BI team will provide even more functionality in the future!
What are the benefits of using this method of Small Multiples?
- No need for having to download custom visuals, which can be difficult to master and understand.
- The analytics behavior if the Matrix and Table visuals are consistent with the base Power BI offering.
- Formatting can be more intuitive.
- Guaranteed support by Microsoft going forward instead of a third party vendor.
Posted by Jacob Olsby