Forum Discussion
Single figure for average measure
This is my data (example):
For each yearmo I want to get the sum of the score.
I then want to get the average of those sums.
I have been able to achieve this using AVERAGEX as per below:
Average of sums =
AVERAGEX(
SUMMARIZE(
'Scores Table',
'Scores Table'[yearmo],
"Yearmo Sum",sum('Scores Table'[score])
),
[Yearmo Sum]
)
(Note that I know as per the example I wouldn't need to summarize but my actual data has more columns that I have excluded here for simplicity).
This gives me a correct total figure of 1.37 as per screenshot below:
What I would like to do is show that value of 1.37 against every yearmo, for example in the chart above I would like a flat line at 1.37 showing the average of all yearmos as context.
To do this I think I would somehow need to make that value of 1.37 persist against every yearmo.
Any ideas on how to do this are much appreciated.
Try creating a new measure like this:
CALCULATE([Average of sums],REMOVEFILTERS('Scores Table'[yearmo]))
And add that to the line chart. I hope this helps.
4 Replies
Use line and clustered column chart. On clusters use values "sum of score", on line use "Average of sums" metric
This doesn't achieve the desired result as the line for "Average of sums" differs for each yearmo:
I want to be able to show a constant line at 1.37 in this case
- dk_dk
Super User
Try creating a new measure like this:
CALCULATE([Average of sums],REMOVEFILTERS('Scores Table'[yearmo]))
And add that to the line chart. I hope this helps.
so simple 😀
Thanks very much
