Average of a monthly count of non numeric values

[GaryO]

Hi,

Help please...I've spent half a day on what I'm sure is very simple!

I am trying to calculate the mean of a monthly count of non numeric values....

I am trying to get an average count by month (CreatedDate) of the number of CaseNumbers in each Subject.

Does that make sense?!

[Sean]

Okay here's the sample data I created.....

Subject	CaseNumber	CreatedDate
S1	C1	1/1/2017
S1	C2	1/15/2017
S1	C3	1/30/2017
S1	C4	2/1/2017
S1	C4	2/15/2017
S2	C1	1/31/2017
S2	C2	1/31/2017
S2	C3	2/2/2017
S2	C4	2/3/2017
S2	C5	2/4/2017
S2	C5	2/5/2017
S2	C5	2/6/2017

1) Create a COLUMN

Year-Month = FORMAT('Table'[CreatedDate], "YYYY-MM")

2) Create these 2 MEASURES

Case Numbers Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[CaseNumber] ),
    ALLEXCEPT ( 'Table', 'Table'[Year-Month], 'Table'[CaseNumber], 'Table'[Subject]
    )
)

Average Case Numbers = 
DIVIDE( [Case Numbers Measure], DISTINCTCOUNT('Table'[Year-Month]), 0 )

Here's the result in a Matrix...

At least that's how I understood your question

[GaryO]

Thanks Sean.

I though this had worked brilliantly but it is not counting the months where there are no cases from a particular subject.

e.g. if March and June had no cases it is dividing by 10 rather than 12 for the year.

[v-qiuyu-msft]

Hi @GaryO,

Would you please share some sample data and screenshots about the desired results for our analysis?

Best Regards,
Qiuyun Yu

[GaryO]

Here you go:

So I wish to get a mean average of the number of CaseNumbers of each Subject in a month but need to include months where there are zero CaseNumbers of a particular Subject.

The solution offered nearly does it but doesn't count the months where there are zero case numbers. Presumably i need a measure to count the number of months first and then use that figure with the total count of CaseNumbers of each Subject.

CaseNumber	Subject	CreatedDate
00001020	Council Tax Empty Property Discount Request	12/09/2016
00001021	Area Maintenance Request - Road Sweeping	28/09/2016
00001022	Area Maintenance Request - Fly-tipping	12/09/2016
00001023	Area Maintenance Request - Fly-tipping	12/09/2016
00001024	Area Maintenance Request - Fly-tipping	12/09/2016
00001025	Council Tax Enquiry	12/09/2016
00001026	Council Tax Single Person Discount Request	12/09/2016
00001027	Council Tax Bereaved Single Person Discount Request	12/09/2016
00001028	Council Tax Change of Address Request	01/10/2016
00001029	Council Tax Change of Address Request	12/09/2016
00001030	Council Tax Enquiry	05/10/2016
00001031	Council Tax Change of Address Request	05/10/2016
00001032	Council Tax Enquiry	12/09/2016
00001033	Council Tax Enquiry	05/10/2016
00001034	Council Tax Enquiry	12/09/2016
00001035	Council Tax Enquiry	12/09/2016
00001036	Council Tax Single Person Discount Request	12/09/2016
00001037	Council Tax Enquiry	13/09/2016
00001038	Council Tax Enquiry	11/01/2017
00001039	Area Maintenance Request - Fly-tipping	11/01/2017
00001040	Council Tax Enquiry	11/01/2017
00001041	Council Tax Change of Address Request	11/01/2017
00001042	Council Tax Enquiry	11/01/2017
00001044	Council Tax Change of Address Request	11/01/2017
00001045	Area Maintenance Request - Fly-tipping	13/09/2016
00001046	Area Maintenance Request - Fly-tipping	13/09/2016
00001047	Council Tax Landlord Change of Tenant Request	13/09/2016
00001048	Council Tax Single Person Discount Request	13/09/2016
00001049	Council Tax Single Person Discount Request	13/09/2016
00001050	Council Tax Single Person Discount Request	01/12/2016
00001051	Area Maintenance Request - Fly-tipping	11/01/2017
00001052	Council Tax Landlord Change of Tenant Request	14/09/2016

[Sean]

@GaryO

1) Add a Year COLUMN

Year = YEAR('Table'[CreatedDate])

2) Then new Case Numbers Measure

Case Numbers Measure = DISTINCTCOUNT ( 'Table'[CaseNumber] )

3) And new Average Cases Measure

Average Case Numbers = 
DIVIDE( [Case Numbers Measure], DISTINCTCOUNT('Table'[Year])*12, 0 )

Okay this will divide within each year by 12 and the overall total by the number of years * 12

Hope this helps!

EDIT: If this is not what you are looking for post the desired outcome based on your sample data.

[GaryO]

@Sean

Nearly!  There aren't a full twelve months though. I think i need to do something around the latest month minus the earliest month.  The results I'd expect based on the data above are below:

Subject	CaseNumber_Count	MonthYear_Count	MeanAverage_Month
Area Maintenance Request - Fly-tipping	7	5	1.4
Area Maintenance Request - Road Sweeping	1	5	0.2
Council Tax Bereaved Single Person Discount Request	1	5	0.2
Council Tax Change of Address Request	5	5	1
Council Tax Empty Property Discount Request	1	5	0.2
Council Tax Enquiry	10	5	2
Council Tax Landlord Change of Tenant Request	2	5	0.4
Council Tax Single Person Discount Request	5	5	1

[Sean · Accepted solution]

@GaryO

Okay I think we got it now...

1) Case Numbers Measure (same as above)

Case Numbers Measure = DISTINCTCOUNT ( 'Table'[CaseNumber] )

2) Num of Months Measure (this is new)

Num of Months =
CALCULATE (
    DATEDIFF (
        EOMONTH ( MIN ( 'Table'[CreatedDate] ), -1 ),
        EOMONTH ( MAX ( 'Table'[CreatedDate] ), 0 ),
        MONTH
    ),
    ALL ( 'Table' )
)

3) And new Average Cases Measure

Average Case Numbers = DIVIDE( [Case Numbers Measure], [Num of Months], 0 )

Here's the result as you expected!

Hope this helps!