topic Re: Cumulative Average of another measure(s) in DAX Commands and Tips

Cumulative Average of another measure(s)

navafolk — Mon, 24 Jun 2024 09:09:23 GMT

Hi pros,

I am facing issue that I would want to create measure of cumulative average of another measure.

The input table looks like:

Date	Item	Amount-base	Amount-added
01-May	LocA	100	11
01-May	LocB	105	10
01-May	LocC	95	9
02-May	LocA	107	11
02-May	LocB	99	9
02-May	LocC	90	9
03-May	LocA	102	11
03-May	LocB	100	11
03-May	LocC	97	9

I prepare some measures:

Total LocA = CALCULATE(sum('Table'[Amount-base])+Sum('Table'[Amount-added]),'Table'[Item]="LocA")

Total LocB = CALCULATE(sum('Table'[Amount-base])+Sum('Table'[Amount-added]),'Table'[Item]="LocB")

and measure of gap between LocA and LocB:

Gap LocA-B = [Total LocA]-[Total LocB]

I need a cumulative average (cumulative since beginning date in the input table to the calculating date). This is what I have tried, but it did not work.

AVG LocA-B = calculate(averagex('Table',[Gap LocA-B]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=max('Table'[Date])))

My desired result would be:

Please help me with this measure. Thank you, all.

Re: Cumulative Average of another measure(s)

MFelix — Mon, 24 Jun 2024 13:10:11 GMT

Hi navafolk ,

Try the following code:

AVG LocA-B = AVERAGEX( FILTER( ALLSELECTED('Table'[Date]), 'Table'[Date] <= MAX('Table'[Date]) ), [Gap LocA-B] )

Re: Cumulative Average of another measure(s)

DataInsights — Mon, 24 Jun 2024 13:13:35 GMT

navafolk,

Try this measure using the WINDOW function:

AVG LocA-B = VAR vTable = ALLSELECTED ( 'Table'[Date] ) VAR vResult = AVERAGEX ( WINDOW ( 0, ABS, 0, REL, vTable, ORDERBY ( 'Table'[Date], ASC ) ), [Gap LocA-B] ) RETURN vResult

Re: Cumulative Average of another measure(s)

navafolk — Tue, 25 Jun 2024 02:14:24 GMT

Super! Just simply what I need, I was too much thinking of CALCULATE, :D.

Thank you MFelix
(*) just a note for myself, I does not work with 'Date Hierarchy' in visual.

Re: Cumulative Average of another measure(s)

navafolk — Tue, 25 Jun 2024 02:06:58 GMT

Great thanks, DataInsights.

WINDOW works well and is too new to me, will research for future usage.