Skip to main content
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Total Average not working

I'm trying to show a changing average line on my chart but i can't seem to work out how to write the expressions.

The red line shows the overall average over the whole time (it uses this expression - avg(TOTAL AGGR(Sum({<Sheet_name={'Wansbeck'},day={'Monday'}>}WGH1),Date)) Despite the dimension being WeekYear 😞

1 .jpeg


I have 3 conditions (PreCovid, Covid & HomeSafe Weekend) that i want to display the averages for.

They run like this:

Week23 2019 - Week 11 2020 = PreCovid

Week 12 2020 - Week 18 2020 = Covid 

Week 19 2020 - Week 31 2020 = HomeSafe Weekend

When i try the expression = avg(Total Aggr(Avg({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),MonthYear)) I just get this:

2 .jpeg

Which just overwrites the overall average.

If i try: avg(Aggr(Avg({<Sheet_name={'Wansbeck'},day={'Monday'},[Named Change]={'PreCovid'}>}WGH1),MonthYear)) Then as you can the average line ends but it ends early (Week 9 instead of Week 11) but also its not a true average as that should be solid at 2.51

3 .jpeg

What am i doing wrong and ideally how could i show one line changing between the three states?

Here is a shot of the data in a straight table:




13 Replies
Creator II
Creator II

Already tried - still an error



@peterderrington Then what is wrong?

Creator II
Creator II

Well as i said earlier @Kushal_Chawda , i want to show an overall average (the red line) and also an average line that shows the average for a set period, PreCovid, Covid and HomeSafe on the same graph. So those other three lines should be flat not wavy, 

Similar to this:


As you can see, the green line has three separate values to it dependant on which time segment they're referring to. 

Former Employee
Former Employee

Best I have to try to help is the following Design Blog post:


To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.