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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
S184019
Advocate III
Advocate III

Summarize Total By Weighted Average

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.png

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

 

IDPersonNumeratorDenominatorMeasureMonth
1234Person A17210.80952417-Sep
5678Person B16180.88888917-Sep
91011Person C73770.94805217-Sep
121314Person D88117-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

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @S184019

To get weighted average as you expected, try this formula

measure2 =
DIVIDE (
    CALCULATE (
        SUM ( Sheet11[Numerator] ),
        FILTER ( ALL ( Sheet11 ), [Month] = DATE ( 2018917 ) )
    ),
    CALCULATE (
        SUM ( Sheet11[Denominator] ),
        FILTER ( ALL ( Sheet11 ), [Month] = DATE ( 2018917 ) )
    )
)

13.png

 

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @S184019

To get weighted average as you expected, try this formula

measure2 =
DIVIDE (
    CALCULATE (
        SUM ( Sheet11[Numerator] ),
        FILTER ( ALL ( Sheet11 ), [Month] = DATE ( 2018917 ) )
    ),
    CALCULATE (
        SUM ( Sheet11[Denominator] ),
        FILTER ( ALL ( Sheet11 ), [Month] = DATE ( 2018917 ) )
    )
)

13.png

 

 

Best Regards

Maggie

S184019
Advocate III
Advocate III

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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