If you are looking for how to do dynamic difference (dynamic delta) in Power BI, you’ve come to the right place. This article is about showing how to calculate the difference (say between actual and budget) dynamically or on-the-fly as you switch from product to product. This is done using the SelectedValue function.
Business Scenario
Here is the business scenario: you work in product development in a manufacturing company. You have many products developed in your product development pipeline, each have different costs for each component like this:
Report Layout
The Power BI report should look like this:
You need to be able to select a product (Product A in the above example) and the actual spend vs budget are displayed in a table, along with the difference.
So you are comparing the cost breakdown of a product to the budget. Another business scenario which is similar to this is comparing the performance attribution of a mutual fund to the benchmark. In this case instead of components, we have industry sectors.
Dynamic Difference
The point here is that the differences are calculated on-the-fly, depending on the user selection. That is the key Power BI functionality that I’d like to show you.
There is a function in Power BI called SELECTEDVALUE, which we need to use for this. We use it to find out which product is being selected.
How To Do It
So let’s create the Power BI report.
Step 1. First, in Excel, type this table, and load it in Power BI as Cost table:
Step 2. Create a calculated table to select distinct values from the Product column:
Product = distinct(‘Cost'[Product])
Step 3. Use it for a slicer so we can select Product A, B and C (filter the Budget out):
Step 4. Create 3 measures: Cost_Budget, Cost_Actual and Cost_Differene as follows:
1. Cost for the budget:
Cost_Budget =
CALCULATE
( SUM(‘Cost'[Cost]),
FILTER(‘Cost’, ‘Cost'[Product] = “Budget”)
)
2. Cost for the selected product:
Cost_Actual =
CALCULATE
( SUM(‘Cost'[Cost]),
FILTER(‘Cost’, ‘Cost'[Product] = SELECTEDVALUE(Product[Product]))
)
3. The difference between the budget and actual:
Cost_Difference = [Cost_Budget] – [Cost_Actual]
Step 4. On the report create a table with Component, Budget, Product and Difference in the Values.
Now when we select Product A it will display the cost of Product A and calculate the difference on-the-fly.
And when we select Product B it will display the cost of Product B and calculate the difference on-the-fly.
Leave a Reply