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

[Vish19]

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)

Entities	MPI - for each entity (by default)	A1	A2	A3	A4	MPI - Desired	A1 - Desired	A2 - Desired	A3 - Desired	A4 - Desired
Entity 1	54.39%	0.600	0.252	0.000	0.000	54.39%	0.600	0.252	0.000	0.000
Entity 2	78.96%	0.000	0.123	0.000	0.000	78.96%	0.000	0.123	0.000	0.000
Entity 3	67.53%	0.000	0.064	0.750	0.000	67.53%	0.000	0.064	0.750	0.000
Entity 4	77.07%	0.000	0.088	0.067	0.000	77.07%	0.000	0.088	0.067	0.000
Entity 5	84.36%	0.000	0.068	0.000	0.000	84.36%	0.000	0.068	0.000	0.000
Entity 6	97.51%	0.000	0.042	0.000	0.000	97.51%	0.000	0.042	0.000	0.000
Entity 7	61.48%	0.170	0.256	0.000	0.000	61.48%	0.167	0.256	0.000	0.000
Entity 8	69.28%	0.000	0.107	0.308	0.000	69.28%	0.000	0.107	0.308	0.000
Entity 9	80.46%	0.000	0.106	0.000	0.000	80.46%	0.000	0.106	0.000	0.000
Entity 10	66.21%	0.000	0.072	0.778	0.000	66.21%	0.000	0.072	0.778	0.000
Total	66.28%	0.140	0.087	0.183	0.000	73.73%	0.077	0.118	0.190	0.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

[v-luwang-msft]

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