Summarize Total By Weighted Average

[S184019]

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

[v-juanli-msft · Accepted 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

[S184019]

Forgot to mention Weighted Average = 114/124 = 0.9193548... ✓

Average for the 4 groups (0.8095+0.8888+0.9481+1)/4 ≈ 0.9116162... ✘