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

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
TParsons
New Member

Getting the total of a divided expression over multiple tables and return the correct grand total.

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 (divide) =
DIVIDE(
[Units (Sum)],
[Employees (Sum)]
)
Measure 2
Units/Employee Total =
Divide(
CALCULATE(
[Units (Sum)],
KEEPFILTERS(
'Year'
),
ALL(
)
),CALCULATE(
[Employees (Sum)],
KEEPFILTERS(
'Year'
),
All(
)
)
)
 
Here is some sample data
 UnitsEmployeesMeasure 1Measure 2Measure 3
Department A10001001011.5234.57
Department B255511.5234.57
Department C45023                          19.5711.5234.57
Total1475128                          11.5211.5234.57
      
  Measure 211.52  
 NeededMeasure 334.57  
 
I'm new to DAX and been taking courses and have learned alot but this problem is still causing me fits and my many google searches and hunting havent provided me with the correct information to solve my problem.
 
So if anyone can help me write a measure that would provide the 34.57 for every row so I can complete my % of the calculation that would be much appreciated.  I'm trying to learn.
 
2 REPLIES 2
TParsons
New Member

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?

amitchandak
Super User
Super User

@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)))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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