In my data set, there is a data field called "Case Record ID" as well as data field for date/time. The data set covers several calendar years. I have a calculation for the number of distinct case record IDs, and I have used it in a bar chart showing the number of distinct case record ids per month for either all years or for a selected year (depending on the filter selection). See Image A.
I would like to add a reference line to the bar chart which shows the average number of distinct case record IDs for each month across all years. I am able to see the averages by month with column grand totals (using average). See image B. But I can't figure out how to calculate an average of a count distinct and then use it for a reference line.
Please find workbook attached.
Hi @Amelia Kohm
Understand and it needs two calc fields:
1 YearFilter TC
str(window_max(max(year([Calendar Year (service entries)]))))
2 AvgCnt of Case Records
window_avg([Count of Case Records])
by 2 ways, one is add reference line another is dual axis
