Power BI Intermediate Level: 41 - CALCULATE Mastery - Understanding Dynamic Visuals

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: Visuals are calculated dynamically by filtering the cached source data, applying the filter context and any custom filter arguments from CALCULATE, and then performing the calculation. Each data point is calculated independently, including totals. When the user interacts with the visuals, e.g. by changing slicers, the visuals are dynamically recalculated.

In this article, let's dive into the way which Power BI makes visuals dynamic. Whenever we use visuals, Power BI dynamically calculates the data points, meaning that if the user applies any filtering through slicers, the visuals get instantly updated. An update of source data on the other hand only happens when the dataset is refreshed, see the article on scheduled online refresh.

Power BI's Dynamic Visuals

Power BI dynamically calculates each data point in a visual in three major steps:

1. Filter the source data according to the data point's filter context in the visual and any other report filters.
2. In case of custom measures, apply any custom filtering from CALCULATE
3. Perform the calculation, such as sum or average, on the source. This way, when the user interacts with the report, e.g. by changing slicers, the visuals are dynamically recalculated.

Performance

A Power BI report when designed properly usually reacts to user input within less than a second. The strong performance comes from Power BI importing and storing (caching) source data in a way that's conducive to performing analytics, e.g. by using columnar storage instead of row storage so calculations can happen quickly. More on performance in a later article.

First Example: Using Measure Sum of Sales

Let's revisit the example from the previous article where we built custom measures for Sum of Sales. Let's investigate of the Sum of Sales for Product C in year 2021 gets calculated according to the above process.

1. Let's gather the relevant filter context in pseudo-code. We have Product = Product C and Year = 2021 from the visual, as well as Year is 2020, 2021, or 2022 from the slicer. These filter conditions are to be combined using And logic. Let's filter our source data accordingly.

2. There is no filter condition within CALCULATE, so we can skip this step.

3. The summarization on the Sales column gets applied, yielding the result €49,086.00.

Second Example: Using Measure Sum of Sales in 2022

Let's do the more interesting example for the same data point but using the measure Sum of Sales in 2022.

We defined the measure as follows:

39 Sum of Sales in 2022 = 
CALCULATE(
    [39 Sum of Sales],
    '39_CALCULATE_for_Filtering_within_Formula'[Year] = 2022
)        

Let's again walk through the process on how Power BI calculates the data point:

1. The filter context is the same as before, i.e. Product C and year 2021, giving us the same filtered data as before.

2. Now we apply our custom filter from CALCULATE. As established in the previous article, conflicting filters from the report get overwritten whereas non-conflicting filters still get applied. So our filter changes to year 2022 (overwritten) and Product C (same as before).

3. The summarization on the Sales column gets applied, yielding the result €38,759.00.

Third Example: Calculations of Totals

Totals are calculated using the same process, they are just additional data points. Totals just have filters from the visual, or even no filter in the case of the grand total. Importantly, totals are calculated independently, they are not calculated from other data points. Let's look at an example. Consider now the Average of Sales instead of the sum.

41 Average of Sales = AVERAGE( '39_CALCULATE_for_Filtering_within_Formula'[Sales] )        

Let's look at how the grand total gets calculated.

1. There is no data point filter for the visual because this is the grand total. There is only the filter from the slicer for years 2020 to 2022.

2. There is no filter condition within CALCULATE, so we can skip this step.

3. The averaging on the Sales column gets applied, yielding the result €28,148.75.

In most cases, this grand total average (green) is different from the average of subtotals (red), the latter would be an average of averages.

In our example, the grand total average happens to be the same as the average of subtotals because we only have one row of data per product and year. But this is an exception. Just keep in mind that Power BI calculates each data point, including totals, independently.

Conclusion

Whenever you feel stuck about why you are getting certain results in visuals, just follow the above process to understand how a specific data point gets calculated. This is especially helpful with complicated visuals and measures. Remember to consider all report filters, including slicers and the filter pane, as well as the filter for each data point in a visual. For most visuals, the data point filters are the x-axis and the legend, or in our case with the Matrix visual just the rows and column filter.

Next up, let's do some filtering with dynamic values within CALCULATE.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?To be published