cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexH330
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.

 

DateValueValueAverageCumulSum (what does show)Cumulsum (what should show)
19696
2261112
3761818

 

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
v-jayw-msft
Community Support
Community Support

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])

 13.PNG

 

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.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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])

 13.PNG

 

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.

View solution in original post

amitchandak
Super User IV
Super User IV

@AlexH330 , Try like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

 

 

@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?

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

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