Max Min and Average Per Day Count

[jlampard40]

Hi - I have a formula for set analysis which calculates my average count per day as follows:

Num(avg({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'}, Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}
Aggr(count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}
Event_Key),Request_Date_Day)),'#,###.')

This plots beautifully on a chart, using my dimension 'Calendar_Month' - so I am essentially displaying average count of an exam per day, over the course of a whole month.  However, I would like to also show the Maximum and Minimum average per day also, for each month.  How can I modify the above formula to show max / min on the same bar?  Many thanks!

[vinieme12]

Calculate AVG manually 

=Num(count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}

Event_Key)

/

count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}

Request_date_day)

,'#,###.')

Then Max

=Num(Max(Aggr(count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}

Event_Key)

/

count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}

Request_date_day)

,Request_date_day,MonthDimension))

,'#,###.')

[jlampard40]

@vinieme12  - thanks for your reply, but this doesn't give me what I need to show on my chart.

I perhaps need three elements in each 'bar' of the chart (per calendar month) to show the average count per day (over the course of the month), then an element in the same bar to show the max count which has occurred, and the min count which has occurred - all throughout the calendar month.

I've attached a visual example using Excel to try to demo what I mean - ideally all the elements (avg per day, max and min) would be represented on each column, per month.

Look forward to your reply - many thanks!