Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I have been searching for an answer to this Dax question. While this problem seems common I am apparently adding some complexity to the issue and I cant get the many answers to work for my unique situation.
I have two fact tables. One of Employees, one of Produced Units. These tables are connected with a relationship by a department Lookup. (one to many)
I've completed the following measures
measure 1
Units | Employees | Measure 1 | Measure 2 | Measure 3 | |
Department A | 1000 | 100 | 10 | 11.52 | 34.57 |
Department B | 25 | 5 | 5 | 11.52 | 34.57 |
Department C | 450 | 23 | 19.57 | 11.52 | 34.57 |
Total | 1475 | 128 | 11.52 | 11.52 | 34.57 |
Measure 2 | 11.52 | ||||
Needed | Measure 3 | 34.57 |
Thank you for your reply, These measures work but are not getting at my intended result.
Perhaps I am asking the question wrong...
I'm trying to get a % of efficiency calculated. So my units/employees is my efficiency, to than get the % of that efficiency I need the total of that column. However, because I am doing a math function on two columns the measures give me the sum of units divided by the sum of employees giving me an overall benchmark efficiency. But to get the % I need the total of the efficiencys by each department.
Can this not be done because Power BI goes row by row and I want a sum of those rows after the math function... Do I need to create another table somewhere to store the answers and then sum them?
@TParsons , Try
Units/Employee Total =
Divide(
CALCULATE(
[Units (Sum)],KEEPFILTERS('Year'),ALL(Employees
)),CALCULATE(
[Employees (Sum)],
KEEPFILTERS('Year'),All(Employees)))
or
Units/Employee Total =
Divide(
CALCULATE(
[Units (Sum)],ALL(Employees
)),CALCULATE(
[Employees (Sum)],All(Employees)))