cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vish19
Regular Visitor

Alternative for AverageX() for a report handling 10M+ rows

I am developing a BI report based on 10+ million rows and I am trying to find a more efficient alternative to AverageX function for calculating Average of a Calculated measure as per data of entities and  then getting the average value of all selected entities, over a selected time period and other filters.

 

I have attached an excel file indicating the sample calculations for MPI (my desired measure). MPI for each entities was calculated basis other 4 calculated measures A1,A2,A3,A4. 

 

The end result that is desired is that while calculating the aggregation of MPI for all entities together, I need the average of all Calculated MPIs of each entity put together and not the default calculated Aggregate MPI basis the underlying calculations and data .

 

I have used the AverageX function for the same, and the function has given the desired result. but it takes a lot of time to process the data on PowerBi desktop apprx 10-15 mins and

 

in PowerBi service, it is not able to calculate the same and gives the error,

"This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.Please try again later or contact support. If you contact support, please provide these details."

 

I have attached the sample excel file.

Formula used (for sample purpose): MPI (desired) = AverageX(Values('Entities'[Entity_ids]),MPI).

same for A1(desired), A2(desired), A3(desired), A4(desired)

 

b68b7bf1-c76d-47e9-8cc2-dc8f23b582e6.png

 

EntitiesMPI - for each entity (by default)A1A2A3A4MPI - DesiredA1 - DesiredA2 - DesiredA3 - DesiredA4 - Desired
Entity 154.39%0.6000.2520.0000.00054.39%0.6000.2520.0000.000
Entity 278.96%0.0000.1230.0000.00078.96%0.0000.1230.0000.000
Entity 367.53%0.0000.0640.7500.00067.53%0.0000.0640.7500.000
Entity 477.07%0.0000.0880.0670.00077.07%0.0000.0880.0670.000
Entity 584.36%0.0000.0680.0000.00084.36%0.0000.0680.0000.000
Entity 697.51%0.0000.0420.0000.00097.51%0.0000.0420.0000.000
Entity 761.48%0.1700.2560.0000.00061.48%0.1670.2560.0000.000
Entity 869.28%0.0000.1070.3080.00069.28%0.0000.1070.3080.000
Entity 980.46%0.0000.1060.0000.00080.46%0.0000.1060.0000.000
Entity 1066.21%0.0000.0720.7780.00066.21%0.0000.0720.7780.000
Total66.28%0.1400.0870.1830.00073.73%0.0770.1180.1900.000

 

The point to be noted above is at row (filtered level) - data should be the same, but at aggregated level is what the calculation is different. Text highlighted in blue is what powerbi calculate by default and red is what is desired

 

Thanks in advance

 

1 REPLY 1
v-luwang-msft
Community Support
Community Support

Hi @Vish19 ,

In my opinion, if you calculate the average based on the data you have provided, this is not difficult, the main problem is still caused by the fact that your source data is too huge.

 the issue occurs when a visual has attempted to query too much data for the server to complete the result with the available resources.

As suggested in the error, you may need to try filtering the visual to reduce the amount of data in the result currently.

refer:

https://community.powerbi.com/t5/Service/visual-has-exceeded-available-resources/td-p/115962 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors