Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |