Forum Discussion
Adding Weekday average line into stacked column chart
Hi, I am trying to add a weekday average line into a stacked column chart, but I cannot find a solution so far
Here is my table
| Date | Team | value | Weekday |
| 4/8/2022 | a | 10 | Fri |
| 4/8/2022 | b | 20 | Fri |
| 4/8/2022 | c | 30 | Fri |
| . | . | . | . |
| 4/15/2022 | a | 10 | Fri |
| 4/15/2022 | b | 12 | Fri |
| 4/15/2022 | c | 30 | Fri |
| 4/16/2022 | a | 10 | Sat |
| 4/16/2022 | b | 73 | Sat |
| 4/16/2022 | c | 16 | Sat |
| 4/17/2022 | a | 22 | Sun |
| 4/17/2022 | b | 45 | Sun |
| 4/17/2022 | c | 50 | Sun |
I want to calculate the average by the weekday over the last weeks, for example, on 15 April (Fri), I get the average of the last two Fridays, 1st April and 8th April. on 16 April (Sat), I get the average of the last two Saturdays, 2nd April and 9th April. Then I can add this as a line in the chart, to compare the performance of the current weekday.
It would be great to get any suggestions or help from you!
Hi chili ,
According to your description, does the following test result meet your requirements.
M = VAR cur_ = SELECTEDVALUE ( 'Table'[value] ) VAR day1 = CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] - 7 = cur_ ) VAR day2 = CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] - 14 = cur_ ) VAR day3 = CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] = cur_ ) RETURN ( day1 + day2 + day3 ) / 3
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello, sorry for the late reply, I was testing out different solutions, but unfortunately the measure above cannot return the correct average number each weekday. I made few changes which inspired by your solution, and I finally got the right numbers for that:
First I created two measures:
1_week before sum =
VAR __max = MAX('Table'[date])
RETURN
calculate(SUMX('Table', 'Table'[value]),'Table'[date]=__max-7)and
2_week before sum =
VAR __max = MAX('Table'[date])
RETURN
calculate(SUMX('Table', 'Table'[value]),'Table'[date]=__max-14)
then I simply get the average by creating the third measure2 weeks average = ([1_week before sum] + [2_week before sum]/ 2
Finally, I used 2 weeks' average to be the value in the constant line, like what you did before. That's how I got the weekday average. Thanks again for your help!
5 Replies
Hi chili ,
You could try a measure like this:
Average Last two weeks = CALCULATE ( AVERAGE ( 'Table'[value] ), FILTER ( 'Calendar', 'Calendar'[Weekday] = SELECTEDVALUE ( 'Calendar'[Weekday] ) && 'Calendar'[Date] >= SELECTEDVALUE ( 'Calendar'[Date] ) - 14 ) )Hopwever, whether it works or not I think depends on whether you are filtering one or all teams. Let me know.
Hi chili ,
According to your description, does the following test result meet your requirements.
M = VAR cur_ = SELECTEDVALUE ( 'Table'[value] ) VAR day1 = CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] - 7 = cur_ ) VAR day2 = CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] - 14 = cur_ ) VAR day3 = CALCULATE ( MAX ( 'Table'[value] ), 'Table'[Date] = cur_ ) RETURN ( day1 + day2 + day3 ) / 3
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello, sorry for the late reply, I was testing out different solutions, but unfortunately the measure above cannot return the correct average number each weekday. I made few changes which inspired by your solution, and I finally got the right numbers for that:
First I created two measures:
1_week before sum =
VAR __max = MAX('Table'[date])
RETURN
calculate(SUMX('Table', 'Table'[value]),'Table'[date]=__max-7)and
2_week before sum =
VAR __max = MAX('Table'[date])
RETURN
calculate(SUMX('Table', 'Table'[value]),'Table'[date]=__max-14)
then I simply get the average by creating the third measure2 weeks average = ([1_week before sum] + [2_week before sum]/ 2
Finally, I used 2 weeks' average to be the value in the constant line, like what you did before. That's how I got the weekday average. Thanks again for your help!
