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.
