Online Magazine

Better visualize and compare data with "small multiples"

What is the best way to compare different key figures? With "small multiples" – i.e., several charts arranged in the same grid. In this hack, I'll show you how to easily generate them with Power BI.

by Salvatore Cagliari

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:

You can easily represent multiple values in one visual.
The individual values in the diagrams are scaled automatically.
The information density in reports is higher.

Let's look at how we can implement this solution.

Noob Hack

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:

Manually remove the measures from the measure selection table that was added to the visual.
Manipulate the filter setting to remove it:

_{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:

Online sales by order date
Online sales by shipping date
Online sales by due date
Online sales by customer
Retail sales

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!

Here you can find more hacks by Salvatore ...

... on the subject of Data Analysis Expressions DAX: Read it now!

... on data analysis with the modulo operator: Find out more!

Pro Hack

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.