Average of a measure VAR

[WilliamLindley]

Hi,

I have created a measure which returns the difference from the previous row’s value (the moving range)

I then need to take the average of all these values.

It works fine using two measures, however as soon as I declare the first measure (the moving range) as a VAR in the second measure (the average) it returns different values for each row whereas I want the average, ie: the same 1 figure over all the rows.

Please help!!!!

here are my measures;

Moving Range =

VAR EarlierTime = CALCULATE (

MAX ( 'Table'[Date] ),

FILTER (

ALLSELECTED ( 'Table'[Date] ),

'Table'[Date] < SELECTEDVALUE ('Table'[Date] )

)

)

VAR EarlierMeasureValue =

CALCULATE ( SUM('Table'[Value]), 'Table'[Date] = EarlierTime )

VAR Moving_Range =

ABS ( EarlierMeasureValue - SUM('Table'[Value]) )

RETURN

Moving_Range

Average Moving Range = CALCULATE(AVERAGEX(DISTINCT('Table'[Date]),[Moving Range]),ALLSELECTED('Table'[Date]))

[V-pazhen-msft]

@WilliamLindley 

Try use summarized table in your measure something like this:

Measure = AVERAGEX(SUMMARIZE('Table','Table'[column], "ToAverage", [Measure]), [Measure])

Best,
Paul

[WilliamLindley]

i have added measures.

i cannot link to the file and dropbox wont work sorry.

@V-pazhen-msft i tried that summarize formula as below, still didnt work

[amitchandak]

Not Clear what avg you need. I created one. But not sure on expected value.

https://www.dropbox.com/s/1so2a6mvm14sake/MovingAvg.pbix?dl=0

[WilliamLindley]

Hi Amit,

thanks for your reply, but unfortuntely this isn't what i was looking for.

I am needing the 'Average Moving Range' which is 32.42 but with only 1 single measure, not having to create the 'Moving Range' and then another measure to average it.

Thanks for your time, if you are able to look again i would be really grateful.

cheers

[amitchandak]

@WilliamLindley, check at same like

Like this

Moving Range 2 = 

VAR EarlierTime = CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALLSELECTED ( 'Table'[Date] ),
'Table'[Date] < SELECTEDVALUE ('Table'[Date] )
)
)
VAR EarlierMeasureValue =
CALCULATE ( SUM('Table'[Value]), 'Table'[Date] = EarlierTime )

VAR Moving_Range =
ABS ( EarlierMeasureValue - SUM('Table'[Value]) )

RETURN
 CALCULATE(AVERAGEX(ALLSELECTED('Table'[Date]),Moving_Range))

[WilliamLindley]

thanks, but still no.

as you can see this simply gives the moving range, i need the average of 32.42 in all rows, (like is showing in the 'average moving range' column

[az38]

Hi @WilliamLindley 

did you try AVERAGEX() function? https://docs.microsoft.com/en-us/dax/averagex-function-dax 

like 

= AVERAGEX(Table, Table[YourMeasure])

[WilliamLindley]

Hi thanks for your reply. Yes I have tried that it just returns the same figure for the average at it does for the actual moving range, eg; the average for that row not all the rows. Calculate / all doesn’t work either once declared as a variable

[az38]

@WilliamLindley 

so, show us your measure statement and data example

[richbenmintz]

Hi @WilliamLindley 

Can you provide a sample pbix file and the expected results, makes trying to create the formula a lot easier