Excel pivot tabel average of count

Question

Hi all,

I have a report with many incidents with date and time in there. With a pivot table I found that I can count the incidents (input is incidentnumber) per day or per month, but I want the average amount of incidents per day, could you help me with this?

Because if I change the value of field settings to average it calculates the average of the incidentnumber (text), which is nothing and results in an error. 

Thank you!

Answer 1

Hi djoekdjoek,

I follow the steps inthis article and success to get the average per day in period of month.

 

The point is add DAX measures:

1. Total Insidents = SUM([insidents])
2. Distinct Day Count = DISTINCTCOUNT([Date])
3. Daily Average = [Total insidents]/[Distinct Day Count]

You should modify the formula according to your column title.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Regards,

Eric

Answer 2

Thank you, but i am searching how to get this data within the Pivot table

Answer 3

Hi,

There is data for only 1 day in that file.  Share data for multiple days in that file please.

Answer 4

I am sorry, I thinks this is the right one: https://docdro.id/UZaG3y6

Answer 5

Hi,

You may download my solution workbook from here.

Hope this helps.