What If Analysis with Power BI - Using Parameter Tables

By Gregg Fox, Thu 01 June 2017, in category Business intelligence

Power BI

  
@

Until Microsoft have updated Power BI with report based Dynamic Parameters, What If analysis will be a little tricky. This post will take you through how to build a What If report in Power BI using manually created parameter tables. If you are more interested in the report itself, than you can view the previous blog post explaining it here.

Components

The Data Model

The sample dataset for this report was simply loaded from a CSV file. In the Query Editor, fact and dimension tables were created from this sample dataset for the sake of clarity. To add the What If component, tables need to be manually created with all the necessary options for parameters. For this report, tariff options ranged between 0 and 75%. A list of values was generated in Excel, and these were then pasted into the Query Editor using the Enter Data icon. Note that one table is required for each parameter.

img

Finally, a table was created to neatly hold the measures, again using the ‘Enter Data’ icon to create a 1x1 table as shown below:

img

As measures can use any table in Power BI as their home table, best practice is to group them all together instead of having them spread out in the table and field list.

When you close and apply the query editor, Power BI will auto-detect the relationship between the fact and dimension tables based on the similarity of the Row ID field in each table. If the relevant relationship is not detected, you can create it manually using Manage Relationships. The result is the data model below:

img

The Measures

After the data model is in place with the necessary parameter tables, create measures that will take into account our parameter selections. Each parameter needs a measure and then the total needs to be calculated taking all parameters into account. To apply the hypothetical tariff amounts, use the logic below:

If the parameter has one and only one value selected and the profit for the category is positive then the tariff is applied, otherwise no tariff is applied.

The DAX (Data Analysis eXpressions) for each parameter is a version of the below for each category:

Furniture Profit = IF(
    HASONEVALUE('Furniture Tariff Options'[Fur. Tariff]) && CALCULATE(SUM('Sales Fact'[Profit]), 'Sales Fact'[Category] = "Furniture") > 0, 
    //Value If True
    CALCULATE(SUM('Sales Fact'[Profit]), 'Sales Fact'[Category] = "Furniture") * (1-FIRSTNONBLANK('Furniture Tariff Options'[Fur. Tariff], 1)), 
    //Value If False
    CALCULATE(SUM('Sales Fact'[Profit]), 'Sales Fact'[Category] = "Furniture")
)

The Total Profit is simply:

Total Profit = SUM('Sales Fact'[Profit])

And the Total Profit Minus New Tariffs is simply the sum of our three newly created parameter based measures:

Total Profit Minus New Tariffs = [Tech Profit] + [Furniture Profit] + [Office Supplies Profit]

The total Tariff Amount or total lost revenue is the difference of our two totals:

Lost Revenue = [Total Profit] - [Total Profit Minus New Tariffs]

The Revenue Change is the inverse of Lost Revenue to show a negative value when revenue decreases:

Revenue Change = -([Lost Revenue])

By hiding the place holder column from the report view, our measure table is now:

img

The Visuals

With our data model and measures in place, it is simple to drag and drop fields to create the visuals for the reports. It is worth noting that the map was shaded according to the Lost Revenue column in order to draw attention to greater total tariff amounts and the map can be used to filter the measures by country. Additionally, the new matrix visual has been used to allow the user to drill into the data.

The Final Report with our What If analysis:

You get extra points if you can identify the source of the sample dataset in the comments!