cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Cumulative Average of another measure(s)

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:

2 ACCEPTED SOLUTIONS
Super User

Hi @navafolk ,

Try the following code:

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

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``````

Proud to be a Super User!

4 REPLIES 4
Super User

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``````

Proud to be a Super User!

Helper IV

Great thanks, @DataInsights.

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

Super User

Hi @navafolk ,

Try the following code:

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper IV

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.