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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Hope this helps!

7 REPLIES 7
Community Champion

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

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.

Community Support

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.
Frequent Visitor

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

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

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.

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:

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

Hope this helps!

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors