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

Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)

For 3 months (1 Qtr) with 5 types of incidents, by date and time.

Count only weekdays.

For each incident, count only days where it occurs.

For all incidents, regardless of type,

count days where at least one type occurred.

With PowerPivot Slicer and PivotChart.

http://www.mediafire.com/file/r19ss5re8v6vhlz/08_26_19a.xlsx/file

http://www.mediafire.com/file/uwqjy9subye20bp/08_26_19a.pdf/file

Answer 3

It is didn't work as I could still not sum. I tried it with the count function, but then the grouping of the date broke down.

Answer 4

Here is the document:

https://www.docdroid.net/J5KPYSV/incident-report-try-out.xlsx

Answer 5

Hi,

Based in your file you can apply this formula in cell G2 and below (see the snip above).

In column F starting from cell F2 give months (as number).

=SUMPRODUCT(--(MONTH($C$2:$C$17271)=F2))/DAY(EOMONTH("01"&F2&YEAR(TODAY()),0))

Hope this helps.

Regards,

IlirU