Hi,
I'm trying to create a cumulative average by a group so
Date 1
Date 2
Date 3 etc and average the cumulative average for each date, so here dvide by 1 then 2 and 3 , not the total number of rows, if date 1 occurs more than once, I've attached a worksheet with an excel table showing what I'd like to do plus pivot with a couple of failed attempts, which I think explains better than this, any help with rolling avarages welcome.
I think I need to use allselected or something.
Richard
Perhaps this will help.
1. Add a column to the source data (No. Dates) populated using: =SUM(MAX(H$6:H6),IF(COUNTIFS(E$7:E7,E7)=1,1,0))
2. Add a measure (Max of Dates) using: =MAX(Table1[No. Dates])
3. Add another measure (Average) using: =[Running total M]/[Max of Dates]
4. In the pivot table at the bottom of the Pivot sheet, drag the Average measure into the Values area
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi,
I'll have a look at what you suggested, I haven't tried it but can see what it's doing, but I managed to sort it out yesterday, and it was pretty obvious when I'd done it,
Will divide by the number of dates, but my error was uisng the running total measure rather than just total, so
Please Login or Register to view this content.
and this does give the average for each distinct date, not all the dates used in the total calculation, I think it's all to do with Calculate and aggregation. I'll mark this up as solved and thanks or the suggestions.Please Login or Register to view this content.
RD
Glad that you got a solution. Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks