• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

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.

Editors Note:  If you want to see this in action/hands on please visit us next week!:  https://eu.powerplatformsummit.com/home

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

multivariate, Power, Reporting
  • Recent Posts

    • Trump’s Note to Biden
    • FSI Blog Series, Part IV: Staying Agile in Trying Times
    • Soci raises $80 million to power data-driven localized marketing for enterprises
    • Conversational Platform Trends for 2021
    • The Great Awakening?
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited