Calculating with average of measure on a row level

[AlexH330]

Hi,

I have a measure that needs to be averaged and operated on at a row level. From this forum, it seems that the below is the correct syntax for the 'average' of a measure

ValueAVerage = CALCULATE([Value], ALLSELECTED('Table'[Date])

This works on a matrix level, however when trying a cumulative sum function for each date, it reverts back to the value for that date alone. This is because ALLSELECTED is referring only to the current date I guess? Though basic operators work (+/-). See below chart.

Date	Value	ValueAverage	CumulSum (what does show)	Cumulsum (what should show)
1	9	6	9	6
2	2	6	11	12
3	7	6	18	18

I'm using the below as a basic cumulative sum function, nothing special:

Cumulsum = CALCULATE([ValueAverage], FILTER(ALLSELECTED('Table'),'Table'[Date] <= MAX('Table'[Date])))

I've also tried the below to no avail

AVERAGEX(SUMMARIZE('Table',Table[Date],"avg",[Measure]),[avg])

I need to have the average of the entire measure, and have each row calculate by that average. How do I do this?

[Anonymous · Accepted solution]

Hi @AlexH330 ,

Please refer these measures.

Measure = CALCULATE(AVERAGE('Table'[Value]),ALL('Table'))

Measure 2 = SUMX(FILTER(ALLSELECTED('Table'),'Table'[date]<=MAX('Table'[date])),[Measure])

 

Best Regards,

Jay

[amitchandak]

@AlexH330 , Try like

Cumulsum = [ValueAverage]*CALCULATE(distinctcount('Table'[Date] ), FILTER(ALLSELECTED('Table'),'Table'[Date] <= MAX('Table'[Date])))

[AlexH330]

@amitchandak clever! that almost worked. However there is 1 pressing issue:

1. When the series is consistent (ie has the same number for multiple days) the sum works correctly. When that value changes within the series, the sum actually subtracts, but it subtracts a random number. After that, the series sums correctly again. See below, date 4 is incorrect (sorry for the formatting, I get an HTML error on these boards)

Date       Value        CumulSum
1             2                2

2             2                4

3             2                6

4             3                5

5             3                8

Any idea what's going on here?