How to do multivariate reporting with Power BI

March 23, 2019 Self-Service BI

What is Multivariate Reporting and when is it helpful?

Multivariate Reporting (also known as Small Multiples) uses a series of visuals with the same measure and same scales but showcases various partitions of the data. The benefit is that you can allow your report consumers to easily compare the same measure across dimensions and all in a single view!

To better understand the value of multivariate report let’s look at following report: Company Revenue by State

Here I have a visual that is NOT with multivariate mode OFF:

You will notice that as you select various views of the data (e.g. Year 2014 & Division Core, Year 2013 & Division Core, Year 2014 & Division Growth, Year 2013 & Division Growth, etc.), it becomes quite difficult to compare any more than two views of the dataset at a time.

Now let’s compare this experience with Multivariate Mode ON.

First select “Show Year” to see visual comparing various years:

Now we can easily compare for multiple years or range of years. What’s a quick insight here? Look at Texas you can easily able to see from 2013-2014 had revenue of ~$ 10M, and 2014 ~$ 5M, and 2013 was not that great (Green = $ 20M, Yellow = $ 10M, Red = $ 0M)

Let’s select “Show Division” to add another dimension to compare.

Now we can compare Year 2013-2014, Year 2013, Year 2014 across the Core, Growth and Minor Division. For example, we can quickly see that Illinois had its most revenue in Year 2014 in the Core Division compared to Year 2013 and the other Divisions: Minor and Growth division.

As demonstrated here, creating multivariate reporting experience for your users, allows them to quickly make comparisons off multiple views of the data.

How do I accomplish this in Power BI?

In Power BI, one way to accomplish this is by using Bookmarks and visual level filters. While there are alternatives ways such as using Custom Visuals, we will focus on the Bookmarks method.

#1 Build your starting visual without any filters.

In our example we are using a map visual and notice there are no applied visual level filters added here:

To get the conditional formatting on the map, go to formatting pane and select Data colors and selected Advanced controls.

Here you see we use Red to represent 0, Yellow to represent 10M and Green to represent 20M. It’s very important that you set up this conditional formatting upfront because later when you start copying and pasting visuals it will be a pain to have to go and set the formatting later for each individual chart. Remember, for your end user to do correct comparisons, every visual needs to be scaled or formatted using the same logic. Therefore, you cannot rely on auto-scaling nor relative formatting because this could lead to misleading, incorrect insights.

#2 Build your filtered visuals

On your starting visual add a filter for a dimension or two that you want to filter and compare on, but do NOT filter the starting visual. For my example, I added filter card on Year and Division, but did not select a value. After you do this, copy this starting visual, then on a new page paste it on the new page for as many views as you want to compare. For example, for the dimension Year I wanted to compare the following: Year range 2013-2014, Year 2014, and Year 2013. So, I pasted the visual 3 times on the new page and filtered them appropriately and added labels.

Additionally, I wanted to compare these above views across Division: Core, Growth, and Minor. On a new page I copied the Year range 2013-2014 visual and pasted it 3 times in the first row, then I copied the Year 2014 and pasted it 3 times in the second row, and finally I copied the Year 2013 and pasted it 3 times in the third row. After, I filtered all the visuals in the first column by Division Core, all the visuals in the second column by Division Growth, and all the visuals in the third column by Division Minor. Finally, I labelled columns so it’s clear to my end users.

#3 Set up Bookmarks and build end-user friendly experience

We’re at the last step; let’s tie all the pieces together. For each page in your report go in and add it a bookmark which can be linked to. This is super easy to do! Just open the bookmarks pane and select “Add” for each page or view of your report.

Now that the bookmarks are created let’s build a user-friendly experience by adding linkable buttons to the canvas. For this start with adding a label for the button as a textbox. Then add a transparent shape with a border on top of the text. Finally, in the action tab set the action to bookmark and select which bookmark you want the button to go to. In my example, I’m using this method to of “Show” vs. “Hide” for my end users to control what they want to see in the comparisons:

And there you have it! All it takes is a bit of copy and paste, visual level filtering, and bookmarks and you’ve created a multivariate report! I hope you found this tutorial useful and it helps you build your own multivariate report for your end users.