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
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?!
Solved! Go to Solution.
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!
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
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
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 |
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.
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 |
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
108 | |
108 | |
91 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |