cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bourne2000
Helper III
Helper III

Calculating Average Cumulative value?

Hi

 

I am having below data

 

bourne2000_0-1634621278635.png

 

 

I want to calculate the average cumulative value . Need output as below. I did this calculation in Excel and sample excel file attached here https://we.tl/t-Y8TKJrgVYB

 

bourne2000_1-1634621440755.png

Average Cumulative of 484.50 for 30th Sep is average of all the values. For 29 sep, average cumulative is 482.86 is average of values from 23 sep to 28 sep. Same is applicable for other dates.

 

Can anyone advise me how to do this in Power BI?

 

2 ACCEPTED SOLUTIONS

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
VahidDM
Community Champion
Community Champion

Hi @bourne2000 

 

Try this:

 

Average cumulative =
CALCULATE (
    AVERAGE ( table[Value] ),
    FILTER ( ALL ( table ), table[Date] <= MAX ( table[Date] ) )
)

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

 

@VahidDM Thanks for your reply. Values are not coming correct. My value column in the table is measure. Is this something an issue?

VahidDM
Community Champion
Community Champion

Can you share the DAX code of the Value measure with a sample of your data?

@VahidDM 

 

Thank you

 

I am having below table

 

bourne2000_1-1634682138006.png

 

 

Here the average column is a measure. Below caulcuation I did 

 

Average Max = AVERAGE(Append1[Max_Price])
Average Min = AVERAGE(Append1[Min_Price])
 Average = ([Average Max] + [Average Min]) / 2
 
Now, I want to calculate the cumulative average of average column
 
I need output as below 
 
bourne2000_2-1634682138723.png

 

I want to calculate the average cumulative value . Need output as below. I did this calculation in Excel and sample excel file attached here https://we.tl/t-N2e7mLeMaF

Cumulative Average is 1045.428571 for 30th Sep is average of all the values. For 29 sep,  cumulative average is 1046.83333 is average of values from 29 sep to 30 sep. Same is applicable for other dates.

 

PBIX file attached here https://we.tl/t-0g7kEMYd7O

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.