Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How does one summarize by weighted average? This seems like it should be a straightforward thing. When I click on the calculated column I am working with my options are:
Weighted average isn't there. Perhaps there is a way to change the word "Total" at the foot of the table to "Weighted Average" and create a calc. if this isn't in the work at MS? I am trying to avoid having to build a completely independent table and overlay it on the existing table. If there is a work around while weighted average is added to the summarize options within desktop, then please read on.
The values I am working with are:
Measure = Numerator/Denominator
ID | Person | Numerator | Denominator | Measure | Month |
1234 | Person A | 17 | 21 | 0.809524 | 17-Sep |
5678 | Person B | 16 | 18 | 0.888889 | 17-Sep |
91011 | Person C | 73 | 77 | 0.948052 | 17-Sep |
121314 | Person D | 8 | 8 | 1 | 17-Sep |
The Dax is:
Sep-17 = DIVIDE( calculate( sum('% New Sch 30'[New Within 30 Qty]),'% New Sch 30'[Month] = "Sep-17"), calculate( sum('% New Sch 30'[Arrived Completed Qty]),'% New Sch 30'[Month] = "Sep-17"))
Any help here is greatly appreciated!
Thanks in advance,
Mark
Solved! Go to Solution.
Hi @S184019
To get weighted average as you expected, try this formula
measure2 =
DIVIDE (
CALCULATE (
SUM ( Sheet11[Numerator] ),
FILTER ( ALL ( Sheet11 ), [Month] = DATE ( 2018, 9, 17 ) )
),
CALCULATE (
SUM ( Sheet11[Denominator] ),
FILTER ( ALL ( Sheet11 ), [Month] = DATE ( 2018, 9, 17 ) )
)
)
Best Regards
Maggie
Hi @S184019
To get weighted average as you expected, try this formula
measure2 =
DIVIDE (
CALCULATE (
SUM ( Sheet11[Numerator] ),
FILTER ( ALL ( Sheet11 ), [Month] = DATE ( 2018, 9, 17 ) )
),
CALCULATE (
SUM ( Sheet11[Denominator] ),
FILTER ( ALL ( Sheet11 ), [Month] = DATE ( 2018, 9, 17 ) )
)
)
Best Regards
Maggie
Forgot to mention Weighted Average = 114/124 = 0.9193548... ✓
Average for the 4 groups (0.8095+0.8888+0.9481+1)/4 ≈ 0.9116162... ✘
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |