Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Performance Issue with AVERAGEX()

Hi community   I have a scenario where I'd like to calculate a % of reached duration. Let's assume I have to Contracts A & B with a given contract duration ("Contract Duration"). The report user ...
averagex
DAX
Performance
PaulOlding's avatar
PaulOlding
Icon for Solution Sage rankSolution Sage
4 years ago

Hi,

 

I tried to recreate this with some test data that had 78k rows, so significantly less than your dataset.

 

Here's the server timings for your measures on this dataset:

 

I condensed it all into a single measure...

Avg Duration = 
VAR _UserValue = MAX('User Selection'[Date])
RETURN
AVERAGEX(
	myTable,
	VAR _Num = MIN(_UserValue, myTable[Contract End]) - MIN(_UserValue, myTable[Contract Begin])
	VAR _Denom = myTable[Contract End] - myTable[Contract Begin]
	RETURN
		DIVIDE(_Num, _Denom)
)

which gives these server timings.  16 storage engine queries down to 3 and a much faster time (for my test data anyway).

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi PaulOlding 

     

    Thank you very much for your support!

    I've tried your formula and the performance is indeed way better.

    Unfortunately I've made a mistake in the definition and the final value should be different.

     

    Let me try to explain the expected results:

    • Step 1: I want to calculate column G based on E and F on the most granular level
    • Step 2: For the total of column G (row 5) this calculation won't be right, so I need the sum.
    • Step 3: I want to calculate the total of column E, which can't be the same formula than the one on the most granular level (C divided by D) or the average (like in my original post), but I need to divide G6 by F6. 

     

    So I need two values which will be different on row level and on total level (G6 & E6) - I guess, there will be might be more than one calculation needed 🙂

     

    Thank you and best regards
    Tom