Long execution time - AVERAGEX measure - How can it be opmitized?

[lcfaria]

Hi all,

I'm having a problem loading some visuals in Power BI that gives me the following error:
You have reached the maximum allowable memory allocation for your tier. Consider upgrading to a tier with more available Memory.

There is one measure that is taking much longer to process than the others and I would like community support for ways I can optimize it.

Basically I need to do a calculation so that it returns values ​​to the managers and then based on this measure I need to calculate the values ​​for the units:

I've tried simulating the problem in an example file, but I don't know if that will be enough, as the real model is much more complex.

I would like to get the same results, but I need to decrease the execution time of the measure Avg Unit.

See that in this example, the time to calculate this measure is twice the time of the 'Avg Manager' measure. However, in the real model its time is 5x longer:

I'm attaching a sample file: https://1drv.ms/u/s!At5JlMR_naI1gf44Gfw5wbitP9k6Mw?e=HDLcK6

Thank you!

[mangaus1111]

Hi @lcfaria ,

try to create these 2 measures 

Measure 1 = 
VAR Avg_Manager =[Average Manager]
RETURN
[Average Manager] * DIVIDE(Avg_Manager,Avg_Manager)

Measure 2 = 
VAR NumUnits= COUNTX(
                 VALUES('dmanager'[unit]),
                 [Measure 1]
                )
VAR _Sum = SUMX(
                 VALUES('dmanager'[unit]),
                [Measure 1]
                )
RETURN
IF (HASONEVALUE('dmanager'[unit]),
      [Measure 1] ,
       DIVIDE(_Sum,NumUnits)
)

and use Measure 2 in your report.

It seems that now the difference is reduced

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111 · Accepted solution]

Hi @lcfaria ,

try to write so. mybe it helps

Measure = 
VAR Avg_Manager = [Average Manager]
RETURN
Avg_Manager * DIVIDE(Avg_Manager,Avg_Manager)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lcfaria]

Hi @mangaus1111 , I apologize for my late reply, but we had another problem and it took all my attention away.

Your solution made the measure to be performed in much less time and it solve my problem. Thank you very much for your help!