How to use Small multiples in Power BI to show multiple Measures | by Salvatore Cagliari | Oct, 2022

By Jessie Hobb On Oct 18, 2022

Imagine you want to show various Measures with the same Axes, for example, Time of Geography. Here I show you how you can do this in one Visual.

Imagine that you have many Measures, and you would like to see them side-by-side, all with the same Axes, for example, Dates, Geography, Product Categories, etc.

You would need to add one Visual and add the first Measures. Then Copy this Visual, change the Measure and align them within the space available on your Report Page so that they look all the same.

It could look like this:

Figure 1 — Multiple Visuals with different Measures (Figure by the Author)

OK, we could tune the appearance to make it look better in the example above, but it’s all additional work that I do not want to cover here.

See my Article about Information Design:

As we are efficient, not lazy, we would like to do this in a better way.

Would it be cool to be able to use small multiples to display multiple Measures in one Visual, like in the Figure below?

Figure 2 — Visual with small multiples (Figure by the Author)

Let’s dive into it how we can realize this solution.

We first need a table with all the measures we want to show in the target Visual.

Something like the table shown in the figure below:

Figure 3 — Measure selection table (Figure by the Author)

I added this table through the Enter data functionality in Power BI Desktop.
The new table doesn’t need to have any relationship to any other table, as it is used only to reference the measure required.

I added the MeasureID column by hand.

Now, I need a Measure to check which MeasureID is selected in the current Filter context and pick the correct Measure.

This Measure could look like the following:

Dynamic Measure =
IF ( HASONEVALUE(‘Measure Selection’[MeasureID])
,SWITCH(MIN(‘Measure Selection’[MeasureID])
,1, [Online Sales (By Order Date)]
,2, [Online Sales (By Ship Date)]
,3, [Online Sales (By Due Date)]
,4, [Online Sales (By Customer)]
,5, [Last Stock by Date Var 2]
,6, [Sales Large Amount_2]
,7, [Weighted Average Sales Amount]
,8, [Margin]
,9, FORMAT([Margin %], “Percentage”)
,[Retail Sales]
)
,[Retail Sales]
)

First, I must check if only one MeasureID is present in the current Filter Context. I do this with the IF() and HASONEVALUE() functions.

If more than one values are present in the actual Filter Context, then I skip to the ELSE branch of the IF() and display the [Retail Sales] Measure. In my case, I define this Measure as my “Default Measure” to show.

If only one value is selected for MeasureID, I can check for it and show the corresponding Measure.

Now, I can add the column Measure Name as a Small Multiple to the target Visual:

Figure 4 — Add Measure selection column as a Small multiple (Figure by the Author)

Now, you get a Visual that can look like this:

Figure 5 — Visual with very small results or non-matching units (Figure by the Author)

The red-marked areas show Measures with either very small values or values calculated as percentages. These two Measures are not very useful here.

What can we do to remove these results:

We can remove these Measures from the Measure added to the Visual
We can manipulate the Filter setting to remove them without changing the Measure:

Figure 6 — Filter settings excluding nun-usable Measures (Figure by the Author)

Now, the result might look like this:

Figure 7 — Result with excluded Measures (Figure by the Author)

Lastly, we can change the appearance of the Small Multiples by changing the Format options:

Figure 8 — Configure Small multiples (Figure by the Author)

Here, we can change the number of columns and rows to set it up how we need it.

We can set one value for the padding between the small multiples, or we can enable Customize padding and set the padding for the rows and the columns as we wish.

Now, imagine we want to see the actual and previous Year’s values.

First, we must create the Previous-Year (PY-) Measures for all the base Measures.

Let’s say we want to do this for the following Measures:

Online Sales (By Order Date)
Online Sales (By Ship Date)
Online Sales (By Due Date)
Online Sales (By Customer)
Retail Sales

I create the PY Measures for each of them, and I create a Measure like the one shown above:

Dynamic Measure (PY) =
IF ( HASONEVALUE(‘Measure Selection’[MeasureID])
,SWITCH(MIN(‘Measure Selection’[MeasureID])
,1, [Online Sales (By Order Date) PY]
,2, [Online Sales (By Ship Date) PY]
,3, [Online Sales (By Due Date) PY]
,4, [Online Sales (By Customer) PY]
,[Retail Sales PY]
)
,[Retail Sales PY]
)

After adding this Measure to the Visual, I got the needed result:

Figure 9 — Result with Previous Year Measures (Figure by the Author)

Now you can play with your phantasy to expand your solution as you need it.

One challenge to building such a solution is managing your Measures.

Usually, your model is not static but is continuously expanded with further Measures, which you want to integrate into your Dynamic Measure.

Fortunately, we can use DAX Studio with Analysis Services Dynamic Management Views to get the full list of all Measures:

SELECT [Name]
FROM $SYSTEM.TMSCHEMA_MEASURES

The following query returns a list of all Measures:

Figure 10 — Get the list of Measures in DAX Studio (Figure by the Author)

Now you can copy the list into Excel and add an Index column. After this, you can copy the new table into the Measure Selection table. With Excel, you can even generate the DAX Measure from the table with simple formulas.

Then you can copy the generated Measure into the Data model.

Small Multiple in Power BI is a great feature to increase the information density in your reports.

Usually, we add Attributes as Small Multiples to split the data by Dimension Elements.

The approach shown here can help display multiple Measures in one Visual innovatively.

One more positive point is that you don’t need to try to synchronize the scaling of the charts between the Measures, as this is done automatically.

At this point, the sky is the limit, and you can use the Small Multiples feature to improve your reporting capabilities in many ways.

Microsoft documentation of the Small multiple Feature: Create small multiples in Power BI — Power BI | Microsoft Learn

Reference Documentation of SSAS Dynamic Management Views: Dynamic Management Views (DMVs) in Analysis Services | Microsoft Learn

I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.