Page Title Here

Below is a list of all the blog posts you are posting that your
visitors might be interested in...

< Go back

Expert story

Power BI and Visio: What If they are better together?

Koen Verbeeck
Koen Verbeeck

Koen is a data professional working at AE. He helps organizations to get insight in their data and to improve their analytics solutions. Koen has over a decade of experience in developing data warehouses, models and reports using the Microsoft data platform. Since 2017 Koen is a Microsoft Data Platform MVP. He has a blog at http://www.sqlkover.com, writes articles for MSSQLTips.com and is a frequent speaker at SQL Server events.

Curious on how you can integrate interactive Visio diagrams in your Power BI reports? Check out this blog post for a detailed walk-through. To top it off, we'll also include the brand new what-if-analysis feature in the mix.

In this blog post we take a look at two new features in the Microsoft Power BI product: the integration of Visio diagrams as a new visual and the ability to create “What If?” scenarios in your Power BI Desktop models (which explains the bad pun in the blog title).

Pads with color diagrams and color shining on background.jpeg

Setting the Scene

If we want to use a Visio diagram in our Power BI repots, we have to create one of course. To keep it simple, I created this small organization chart in Visio:

image2017-8-22_10-58-18.png

It represents a department where each employee is responsible for sales. Some sample data:

image2017-8-22_10-59-29.png

The ID column of the sample data corresponds with the text inside the shapes of the Visio diagram. This is important when we want to have these mapped automatically in Power BI Desktop. The Visio document is stored in a OneDrive for Business account. This is important as Power BI needs access to this document.

Let’s import our sample data into Power BI Desktop. We choose Excel as the source of the data:

image2017-8-22_11-0-1.png

After picking the Excel workbook in the navigator, we select the table and load it directly into the model.

image2017-8-22_11-0-23.png

Next, we’re going to define the data model a bit further. Let’s sort the month according to the month number, instead of alphabetically. 

image2017-8-22_11-0-48.png

We’re also adding a measure with the following formula:

NetSales = SUM(Revenue) * 0.9

At the moment, we’re offering the customers a 10% discount. The last step is to import the custom Visio visual into Power BI Desktop. The easiest way to do this is through the store (make sure you have updated Power BI Desktop to the latest version):

image2017-8-22_11-4-26.png

Simply click on Add to have the visual imported.

visio008.png

Now we can start building our report.

Adding the Viso Visual

First, add the visual to the canvas by simply clicking on the icon.

image2017-8-22_11-6-9.png

The next step is to specify the mapping between the shapes and the data. We can do this by dragging the ID column from our sample data to the ID field of the visual.

image2017-8-22_11-6-35.png

The visual will now ask for the URL to the Visio diagram. You can find this URL by navigating to the OneDrive for Business folder and opening the diagram in the browser. This will launch Visio Online. You can simply copy paste the URL (which should contain WopiFrame.aspx) from the browser.

image2017-8-22_11-7-7.png

It’s possible you have to log into OneDrive for Business. Finally, the diagram is shown in Power BI Desktop:

image2017-8-22_11-7-30.png

Now we need to specify a measure for the Values property. This will be the NetSales measure. The visual will add this measure as text:

image2017-8-22_11-8-4.png

However, we want it to color the shapes instead. Let’s change “Display as” to Colors and specify some ranges for the different values:

image2017-8-22_11-8-27.png

I choose three variations of the color green, where the darker the color, the better the results. This is a more color blind friendly method of representing data. Using the Play Axis custom visual, we can see the colors change when we change between months:

colors_animation.gif

The Visio visual is also fully interactive. Clicking on other visuals will zoom to the relevant shape in the diagram. The other way around, clicking on a shape will filter all other visuals.

interaction.gif

Now that we’ve got the diagram working, we can add a what-if scenario.

Adding What-if-Analysis

Let’s make this report a bit more interactive by adding a What-if-Analysis. In such an analysis, you can manipulate a parameter to see what an effect a value would have on your current numbers. In this example, we’re going to manipulate the discount given to customers.

First, create a new parameter in the Modelling tab in the ribbon (keep in mind this functionality is currently only available for models with imported data and not for live sources):

image2017-8-22_11-13-46.png

In the pop-up, you can specify minimum and maximum values, as well as an increment and a default value:

image2017-8-22_11-14-10.png

The values range between 0.5 (50% discount given) and 1.5 (50% extra invoiced to customers, for when the company becomes evil). The following objects are generated:

  • A table holding all the possible values for the parameter
  • A measure called Discount Value holding the current selected value of the parameter
  • A slicer on the report allowing you to easily change the value of the parameter

In order to use this parameter, we adapt the NetSales measure to use the parameter value:

image2017-8-22_11-14-45.png

When we change the slicer to a play axis, we get the following analysis:

whatif.gif

When you use highlighting on the bar chart as interaction, you can easily compare the value from the what-if scenario against the default value.

Conclusion

This blog post has shown how you relatively easy embed Visio visuals in your Power BI reports. Adding what-if analysis allows you to enrich your reports even more and gives an additional layer of interactivity.

Did you find our walk-through helpful? Share it with others!