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

Hi djoekdjoek,

You can check my answer and see if I get the point.

Firstly, I add a count column and fill the column with 1 and create the Pivot Table.

Then drag field as following picture, then you will see how many the events happen per day.

 

Regards,

Eric

Answer 3

Hi Eric,

Thanks for your answer, but this is not what I mean. You made only 3 types of incidents. But all the incidents are different. I already have the amount of indicidents per day or per month. But I want the average amount of incidents per day in a specific month. I did it by hand now, since the calculation is not difficult, but I want it to be in the pivot it self. And the data is in there, so it should be possible. Also now I took the full month, but maybe only incidents happened on the weekdays.

See below:

Answer 4

Hi,

This is possible with the PowerPivot.  Share the link from where I can download your workbook.

Answer 5

Hi djoekdjoek,

If you need further help, you can post your latest condition.

Regards,

Eric