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

0 votes
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?
Nov 7 in Power BI by Evanjalin
• 7,890 points
59 views

1 answer to this question.

0 votes

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.

answered Nov 7 by pooja
• 8,470 points

Related Questions In Power BI

0 votes
0 answers

How do I create a rolling time window in Power BI visuals, such as the past 7 or 30 days?

How do I create a rolling time ...READ MORE

Nov 14 in Power BI by Evanjalin
• 7,890 points
53 views
0 votes
1 answer

How do I know who in my organization has a Power BI account?

You can view the Azure Active Directory ...READ MORE

answered Oct 15, 2018 in Power BI by Hannah
• 18,520 points
1,155 views
0 votes
1 answer

I have a web API with basic authorisation and username and password. How do i connect to it in Power BI?

Hey, @There, I found your query is quite ...READ MORE

answered Oct 29, 2020 in Power BI by Gitika
• 65,770 points
3,626 views
0 votes
0 answers

How do I optimize DAX queries for better performance in Power BI?

Oct 11 in Power BI by anonymous
• 7,890 points
155 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
6,789 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
4,287 views
0 votes
1 answer

How can I search for multiple strings?

A simple solution is this: List.ContainsAny(Text.SplitAny("This is a test ...READ MORE

answered Oct 24, 2018 in Power BI by Upasana
• 160 points
4,731 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 13,490 points
1,903 views
0 votes
1 answer

How do I optimize DAX queries for better performance in Power BI?

To optimize DAX queries for better performance ...READ MORE

answered Oct 22 in Power BI by pooja
• 8,470 points
126 views
0 votes
1 answer

How do you troubleshoot inconsistent visuals or slicer behavior across different pages in a Power BI report?

Investigation of various potential causes of differing ...READ MORE

answered Nov 4 in Power BI by pooja
• 8,470 points
61 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP