Calculating Dynamic Percentage Of Total Change Using Power BI Time Intelligence

In this tutorial, I’ll show you a dynamic way of getting the Percent of Total Change in Power BI using simple measure branching techniques and time intelligence calculations in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

For example, if you want to work out the difference in contribution of Sales for a particular dimension and its change from year to year.

When you’re new to Power BI, the first thing you’d usually do is break down or filter all your percentages by the specific year. The most common is using the Percent of Total formula pattern in Power BI. You’d be using the FILTER function inside CALCULATE.

This will enable you to work out the difference, for example, in 2019, 2018, and 2017. This method will give you a solution.

However, there’s a better way of approaching this problem. All you need is to utilize simple Power BI measure branching techniques and time intelligence calculations.

When you start combining these two together, you can get really powerful dynamic calculations. You don’t need to place additional filters on specific years in your model.

Table of Contents

Calculating Percent Of Total Revenue

This is how I calculated the Percent of Total Revenue.

In the table, you can see that I have my Total Revenue and Percent of Total Revenue.

The Percent of Total Revenue has the same value with the measure in this second table.

It’s basically the sum of the percentages for the different years. It’s a dynamic calculation of the Total change.

The three years shown in the table are those that I’ve selected in the Year slicer.

This is what’s creating the context in the report.

But then, there’s also a matrix that’s breaking down those three years inside the table.

So, there’s no need to create a new measure for 2016 or 2017.

All you need to do is use the Percent of Total Revenue formula technique.

This formula is dividing Total Revenue for each Product per Year by the Total Revenue of ALL Products.

For the denominator part of this formula, I’ve removed all the filters from Products.

I’m getting the Total Revenue from the Year. So, the Total is appearing in the bottom of every single row in the table.

This is why I’m getting Total Revenue divided by the Yearly Total. That’s how to get the percentage.

So, you don’t need to do additional work to get these results.

Calculating The Dynamic Change Between Years

Now, if you want to compare the difference from Year to Year, all you need to do is branch out using time intelligence.

So, let’s create a new measure.

I’m going to name this measure Yearly Diff Percentage of Total Revenue.

Then, all you need to do is type in:

This formula uses simple Power BI time intelligence techniques.

Then, I copy this across the matrix.

I now have three measures.

I can then put the measure Yearly Diff Percentage of Total Revenue into my matrix by dragging it into the Values pane and removing Percent of Total Revenue.

And then change the format to a percentage.

You can see in the table that the Year 2016 has higher numbers.

So, if you want to add some additional logic, you can put this in the formula:

If the calculation equals zero, it will return BLANK. If not, it will run the calculation. This will get rid of the column for the Year 2016.

So, there’s now a dynamic change and difference between one year and the next.

So, if you do a bit of auditing, you’ll see that the difference between the Year 2017 and the Year 2016 is 0.04%. The difference between the Year 2018 and the Year 2017 is 0.08%.

Conclusion

You don’t need to create a lot of additional measures to break out the difference between one year and the next.

Always think dynamically. Think about how you can utilize the natural filtering that will come from your data model.

This is going to go a long way in terms of finding solutions. If you go ahead and try this out in your own models, you’ll see how simple it is to actually do this inside Power BI.

I hope you learned a lot from this tutorial.

All the best,

Sam