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.

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

Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...