Skip to main content
cancel
Showing results for 
Search instead 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

Reply
navafolk
Helper IV
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:

DateItemAmount-baseAmount-added
01-MayLocA10011
01-MayLocB10510
01-MayLocC959
02-MayLocA10711
02-MayLocB999
02-MayLocC909
03-MayLocA10211
03-MayLocB10011
03-MayLocC979

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:

navafolk_0-1719220154005.png

 

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

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @navafolk ,

 

Try the following code:

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

MFelix_0-1719234593883.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

DataInsights
Super User
Super User

@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

 

DataInsights_0-1719234778137.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
DataInsights
Super User
Super User

@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

 

DataInsights_0-1719234778137.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Great thanks, @DataInsights.

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

MFelix
Super User
Super User

Hi @navafolk ,

 

Try the following code:

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

MFelix_0-1719234593883.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.