Question

How do I calculate a rolling average or cumulative total in Power BI without performance issues?

I’m trying to calculate a rolling average or cumulative total in Power BI, but the calculations are causing performance issues, especially when working with large datasets. The visualizations are slow to load, and the data refresh is taking longer than expected. What are the best practices for calculating rolling averages or cumulative totals efficiently in Power BI, and how can I optimize the performance of these calculations?

Answer 1

When working in Power BI, especially with bigger datasets and the need to calculate rolling averages and cumulative totals, performance may, at times, be affected. Here is a more human-centered way to address such challenges and improve the maintenance of the performance of your reports:

1. Use DAX Functions Optimally
When devising solutions involving rolling averages or cumulative totals, some DAX functions perform better than others, such as SUMX, AVERAGEX, and CALCULATE in conjunction with FILTER. To calculate a cumulative total, use CALCULATE and ALLSELECTED, which will appropriately respect context management and, in turn, help perform performance optimizations by containing calculations to the data on the screen.

For example, when designing a DAX measure for a DAX measure that produces a cumulative sum, your measure might be as follows:

CumulativeTotal = 

CALCULATE( 

SUM('Sales'[Amount]), 

FILTER( ALLSELECTED('Sales'[Date]), 

'Sales'[Date] <= MAX('Sales'[Date])

 ) 

)

This method prevents Power BI from calculating totals based on the entire dataset, instead utilizing only visible data, which helps decrease load time significantly.

2. Pre-aggregate Data in Power Query or SQL

Whenever possible, carry out calculations outside Power BI in Power Query or, at the most, at the data source. This means that if the connection is to a database, for example, then the data aggregation should take place within the SQL. Greatly detailed visualization shows how, thanks to pre-calculated detailed oriented rolling averages or placed fully summed figures, very complex strs of DAX are avoided when calculating huge datasets and figures. You can make use of Power Query’s Group By feature when you want to pre-aggregate data before it reaches your Power BI model.

3. Use Incremental Refresh for Large Datasets

In the case of very large datasets, think about applying incremental refresh. This option enables Power BI to update only the data that is new or has been changed rather than the entire data set every time it is processed. In addition, the reversible application of incremental refresh along with DirectQuery or Import modes allows for even more data freshening and report opening periods, particularly when the calculation of a rolling average or cumulative total changes in data refers to very fresh data.

Hence, considering all these rules appropriate for DAX usage, DAX pre-aggregation if possible, and incremental DAX refresh, rolling calculations in Power BI can be performed without performance cost.

Answer 2

To enable rolling averages or cumulating summations with efficiency in Power BI without a hitch regarding performance:

Use DAX Efficiently: For rolling averages, employ AVERAGEX with DATESINPERIOD, and for cumulative totals, CALCULATE with FILTER. Don't iterate over large datasets with complex expressions.

Pre-aggregate Data: This helps pre-aggregate data in Power Query or even in the data source so that fewer data can be processed.

Optimize Relationships: This also involves properly creating relationships with the right indexing in the source data, which speeds up calculation.

Reduce Visuals and Measures: Such calculations would minimize the number of visuals and measures that utilize them without filling a comparatively large proportion of slicers or filters that add complexity to the queries.

These changes can greatly affect performance in large data sets.