Weighted Average from Measures

[st-mb]

Hello,

I'm trying to calculate the weighted average of a contribution score with some existing measures, but I just can't seem to get this one right.

There's three work orders tables (site a & site b tables are the same layout, site c's layout is different) and an employee table. They are linked together using the employee's UserID. I currently use the following measures to track the work completed (each row is considered a unique work order):

Site A & B Work Completed = COUNTA(UNION('Site A Work','Site B Work'),[UserID]

Site C Work Completed = COUNTA('Site C Work'),[UserID]

Total Work Completed = [Site A & B Work Completed] + [Site C Work Completed]

I currently use the following measures to get an individual's contribution percentage and an unweighted team average:

Contribution Percentage = DIVIDE([Total Work Completed],CALCULATE([Total Work Completed],ALL(Employee[UserID])))

Average Contribution Percentage (Unweighted) = DIVIDE([Contribution Percentage],DINSTINCTCOUNT(Employee[UserID]),0)

What I would like to achieve is a weighted average of the contribution percentage to use in a card visual, as seen in this Excel formula:

I'm hoping there's an easy way to go about this without having to merge/modify the existing tables.

Thanks for your time!

[v-cherch-msft · Accepted solution]

Hi @st-mb

You may refer to below measure. If it is not your case, please share some data sample.

Measure =
SUMX (
 SUMMARIZE ( Table, Table[Name] ),
 [Total Work Completed] * [Contribution Percentage]
)
 / [Total Work Completed]

Regards,

Cherie