Forum Discussion
Calculate daily average across months
Hello,
I am attempting to calculate the average number of incidents reported per day across the months of September and October, which have 30 and 31 days respectively. I have 25 total incidents which fall on various days during these months (10 in October and 15 in September).
I have a pie chart visualization, Drill Down Pie PRO, and a text box which includes a measure for calculating the daily average. When I select one of the months in the pie chart, it correctly calculates the daily average in the text box. For example, if I select September, it correctly calcultes the daily average as 0.5 (15 incidents divided by 30 days).
The problem is when I do not select either month. In this case, it incorrectly displays the average as 0.07. It seems like the measure is correctly counting the total number of incidents (25). So, it looks like when I don't select either month, the measure is dividing by 365 days for some reason (25 incidents divided by 365 equals about 0.07).
My measure is count of incident classifications divided by disinct count no blank of the date reported, date. I've experimented with averagex and date tables, but I lack the experience to make this work. I'd greatly appreciate any help anyone can provide. I apologize there isn't a chart or table within this message. Every time I tried to insert a table, my message was flagged as spam.
Thank you.
I just figured it out. I created two measures:
Measure = IF(COUNT(Base[Date Reported].[Date])=365, 61, COUNT(Base[Date Reported].[Date]))Measure 2 = COUNT(Base[Classification]) / [Measure]
6 Replies
Classification Date Reported Incident 1 9/1/2021 Incident 2 9/3/2021 Incident 3 9/5/2021 Incident 4 9/7/2021 Incident 5 9/9/2021 Incident 6 9/11/2021 Incident 7 9/13/2021 Incident 8 9/15/2021 Incident 9 9/17/2021 Incident 10 9/19/2021 Incident 11 9/21/2021 Incident 12 9/23/2021 Incident 13 9/25/2021 Incident 14 9/27/2021 Incident 15 9/29/2021 Incident 1 10/2/2021 Incident 2 10/4/2021 Incident 3 10/6/2021 Incident 4 10/8/2021 Incident 5 10/10/2021 Incident 6 10/12/2021 Incident 7 10/14/2021 Incident 8 10/16/2021 Incident 9 10/18/2021 Incident 10 10/20/2021 - Measure = COUNT(Base[Classification]) / DISTINCTCOUNTNOBLANK(Base[Date Reported].[Date])I apologize for all of the replies. I'm trying to escape the spam filter.
Gabriel_WalkmanContinued Contributor
What should happen when no months are selected?
The textbox should display the average reported incidents per day as about 0.41 (25 incidents divided by 61 days), but the measure is dividing the incidents by 365 days.
I just figured it out. I created two measures:
Measure = IF(COUNT(Base[Date Reported].[Date])=365, 61, COUNT(Base[Date Reported].[Date]))Measure 2 = COUNT(Base[Classification]) / [Measure]- Gabriel_Walkman
This will not work every fourth year, which will have one more day in them.
Why do separate measures?
I recommend having an if depending how many months have been selected, ie. diatinctcount( 'Calendar'[Month]) > 2).
I hope you are using a separate calendar table?