Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
GaryO
Frequent Visitor

Average of a monthly count of non numeric values

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?!

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@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! Smiley Happy

Average Number of Cases3.png

Hope this helps! Smiley Happy

View solution in original post

7 REPLIES 7
Sean
Community Champion
Community Champion

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

 

SubjectCaseNumberCreatedDate
S1C11/1/2017
S1C21/15/2017
S1C31/30/2017
S1C42/1/2017
S1C42/15/2017
S2C11/31/2017
S2C21/31/2017
S2C32/2/2017
S2C42/3/2017
S2C52/4/2017
S2C52/5/2017
S2C52/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...

 

Average Number of Cases.png

 

At least that's how I understood your question Smiley Happy

GaryO
Frequent Visitor

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.

 

 

Hi @GaryO,

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

CaseNumberSubjectCreatedDate
00001020Council Tax Empty Property Discount Request12/09/2016
00001021Area Maintenance Request - Road Sweeping28/09/2016
00001022Area Maintenance Request - Fly-tipping12/09/2016
00001023Area Maintenance Request - Fly-tipping12/09/2016
00001024Area Maintenance Request - Fly-tipping12/09/2016
00001025Council Tax Enquiry12/09/2016
00001026Council Tax Single Person Discount Request12/09/2016
00001027Council Tax Bereaved Single Person Discount Request12/09/2016
00001028Council Tax Change of Address Request01/10/2016
00001029Council Tax Change of Address Request12/09/2016
00001030Council Tax Enquiry05/10/2016
00001031Council Tax Change of Address Request05/10/2016
00001032Council Tax Enquiry12/09/2016
00001033Council Tax Enquiry05/10/2016
00001034Council Tax Enquiry12/09/2016
00001035Council Tax Enquiry12/09/2016
00001036Council Tax Single Person Discount Request12/09/2016
00001037Council Tax Enquiry13/09/2016
00001038Council Tax Enquiry11/01/2017
00001039Area Maintenance Request - Fly-tipping11/01/2017
00001040Council Tax Enquiry11/01/2017
00001041Council Tax Change of Address Request11/01/2017
00001042Council Tax Enquiry11/01/2017
00001044Council Tax Change of Address Request11/01/2017
00001045Area Maintenance Request - Fly-tipping13/09/2016
00001046Area Maintenance Request - Fly-tipping13/09/2016
00001047Council Tax Landlord Change of Tenant Request13/09/2016
00001048Council Tax Single Person Discount Request13/09/2016
00001049Council Tax Single Person Discount Request13/09/2016
00001050Council Tax Single Person Discount Request01/12/2016
00001051Area Maintenance Request - Fly-tipping11/01/2017
00001052Council Tax Landlord Change of Tenant Request14/09/2016

 

Sean
Community Champion
Community Champion

@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 )

Average Number of Cases2.png

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

 

Hope this helps! Smiley Happy

 

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

GaryO
Frequent Visitor

@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:

 

SubjectCaseNumber_CountMonthYear_CountMeanAverage_Month
Area Maintenance Request - Fly-tipping751.4
Area Maintenance Request - Road Sweeping150.2
Council Tax Bereaved Single Person Discount Request150.2
Council Tax Change of Address Request551
Council Tax Empty Property Discount Request150.2
Council Tax Enquiry1052
Council Tax Landlord Change of Tenant Request250.4
Council Tax Single Person Discount Request551
Sean
Community Champion
Community Champion

@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! Smiley Happy

Average Number of Cases3.png

Hope this helps! Smiley Happy

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.