# 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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Unpacking The Evolution Of Power BI – From Launch to Now

Microsoft Power BI is a dynamic business intelligence platform that has revolutionized the way...

## Finding Patterns In Your Data Using Internal Logic in Power BI

Today Iâ€™m going to showcase how you can discover patterns in your data when using Power BI. This is an...

## Report Builder Tutorial: Sort & Filter Data In Paginated Reports

In this Report Builder tutorial, youâ€™ll learn how to sort and filter the data in your paginated...

## Enterprise DNA October Updates – Milestones, Courses and More

It's the final quarter of the year and we're all set to end 2021 with a bang. We're proud to say that...

## Creating A Pareto Chart In Power BI – Advanced DAX

This blog tutorial showcases a really interesting example around Pareto analysis. Iâ€™m going to teach...

## Power BI Tooltips On Report Images

With the recent Power BI challenges that we run in Enterprise DNA, I've become a huge fan of the Power...

## Best Practices In Using The Q&A Feature In Power BI

The Q&A feature or the natural language feature is seriously one of the most incredible features...

## Perpetual Holiday Calendar – POTW #12 (Power Query Solution)

In this blog post, we'll explore my solution to Problem of the Week #12. For this challenge, we...

## What Is DAX Studio In Power BI Desktop

This tutorial will discuss what DAX Studio is and how it can be used to optimize your DAX codes. DAX...

## DAX Queries Using Variables And Important Functions

This tutorial will discuss about variables, including the FILTER and CALCULATE functions. Youâ€™ll...

## Dynamic Tooltip In Power BI With Embedded Charts

I want to show you an awesome trick for the visualizations in your reports. You can use a dynamic...

## Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...