cancel
Showing results for
Did you mean: Frequent Visitor

## Calculating with average of measure on a row level

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?

1 ACCEPTED SOLUTION  Community Support

Hi @AlexH330 ,

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

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

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
3 REPLIES 3  Community Support

Hi @AlexH330 ,

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

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

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.  Super User IV

@AlexH330 , Try like

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

Proud to be a Super User! Frequent Visitor

@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? Announcements #### Welcome to the User Group Public Preview #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps Top Solution Authors
Top Kudoed Authors
Users online (4,520)