Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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;
Try use summarized table in your measure something like this:
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
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
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
@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))
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
did you try AVERAGEX() function? https://docs.microsoft.com/en-us/dax/averagex-function-dax
like
= AVERAGEX(Table, Table[YourMeasure])
so, show us your measure statement and data example
Can you provide a sample pbix file and the expected results, makes trying to create the formula a lot easier
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |