By means of so-called "small multiples" – different charts arranged in a grid with the same axes and scales (see also Fig. 2) – you can compare your data more easily and thus not only analyze it better, but also increase the information density of reports.
But how exactly can these charts be implemented? Of course, you could do it manually, however, not without a multitude of individual, time-consuming steps until finally all data can be visually displayed with the desired and also comparable values.
A much more recommendable option is to work with the "Small Multiple" function in Power BI. In the following hacks, I'll show you step by step how this works.
Imagine you have numerous key figures, and you want to put them in context with each other in the best possible way so that you can easily compare them. If you do this in a "conventional" way, your result might look like Fig. 1:
Figure 1: Multiple visuals with different values
You get several individual diagrams, which are difficult to compare because of different scales.
A much better option would therefore be if all values were displayed in a single visual and could be compared on the basis of the same axes and scales as in Fig. 2:
Figure 2: Visual with «small multiples»
Realizing this with "Small Multiples" in Power BI gives you the following advantages:
Let's look at how we can implement this solution.
First, we need a table with all the measures that will end up in the visual. I have added this table via the "Enter Data" function in Power BI Desktop.
This could look as follows:
Figure 3: Value selection table
The new table does not need to have a relationship to any other table, as it is only used to reference the required values. I have added the column "MeasureID" by hand.
Next, I have to check if there is only one "MeasureID" in the current filter context. For this I use the functions IF() and HASONEVALUE().
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] )
If there is more than one MeasureID in the current filter context, I jump to the ELSE branch of the IF() function and display the [Retail Sales] measure. In my case, I define this value as my "Default Measure".
The "Default Measure" is displayed when no measure is selected from the measure selection table. This way I make sure that a result is always displayed and no error occurs.
If only one value is selected for "MeasureID", I can check it and display the corresponding key figure.
Now I can add the column "Measure Name" to the "Small Multiples":
Figure 4: Add “Measure Name” to “Small Multiples”
After that you will get the result shown in Fig.2.
However, not all diagrams in our example correspond to the desired result yet. For example, the diagrams marked in red in Fig. 5 either have very small values compared to the others or are given as percentages – and are thus not very useful.
Figure 5: The bars of the diagrams marked in red turn out very small or their units do not match.
We have the following 2 possibilities of removing these results:
Figure 6: Filter settings to exclude measures that cannot be used
The result could look like this:
Figure 7: Result with excluded measures
Finally, we can change the appearance of the "Small Multiples" by adjusting the format options:
Figure 8: Configure «Small Multiples»
The number of columns and rows can be customized and set up as needed.
The spacing (padding) between the individual diagrams can also be predefined uniformly or set separately for the rows and columns.
This opens up a whole new set of possibilities. Imagine you want to see the following values of the current and the previous year:
For this, I create previous year's measures (PY) and proceed as described 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] )
In our example, I get the following result:
Figure 9: Result with values from the previous year
Now you can get creative and expand your solution as needed!
You don't want your diagrams to remain static, but to be able to be dynamically extended by new values? To do this, you must be able to manage your measures and integrate them as "Dynamic Measure".
For this you can use DAX Studio to query the "Analysis Services Dynamic Management Views".
The following query will give you a list of all measures:
Figure 10: Retrieving the list of measures in DAX Studio
You can first copy this list into Excel and add an index column. Then you can copy the new table into the "Measure Selection" table.
With Excel, even the "Dynamic Measures" can be generated from the table with a simple formula.
Afterwards, the generated values can be copied into the data model.
Create small multiples in Power BI - Power BI | Microsoft Learn
Dynamic Management Views (DMVs) in Analysis Services | Microsoft Learn
I use the sample dataset from Contoso as in my previous articles. You can download the ContosoRetailDW dataset for free from Microsoft.
The Contoso data can be freely used under the MIT license, as described here.