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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
croberts21
Responsive Resident
Responsive Resident

PBI will not sum hours correctly

I have PBI Desktop v March 2023. I connect to a Postgresql v9.6.17 database. 

 

I have 2 tables, Jobcost with fields like esthours (which is estimated hours), StartDate and Empid. I have another table Employee with a field like Empid, firstname, Lastname. Jobcost.empid is connected to Employee.empid in the relationships pane. Empid in both tables is a string with a length of 6.

 

Here are the tables and fields again.

Jobcost will have many entries per day for each Empid. 

Jobcost[Empid]

Jobcost[Esthours]

Jobcost[StartDate]

 

Employee[Empid] (connected to Jobcost[Empid])

Employee[firstname]

Employee[Lastname]

 

I'm trying to make a matrix to sum hours in a given startDate range by employee. PBI should just sum this by Empid but it doesn't in this case. PBI works in other cases in other PBIX files. 

 

But pbi gives me all hours summed in the table for every employee so all the values for all employees are the same. My sum is in a measure which looks like: 

Est Hours = SUM(JobCost[estcost])
Example incorrect output: 
John Smith    4001
Sally Green    4001
Jim Jones      4001
 
The esthours should be different for each employee.
 
I am unable to upload real data at this time. 
 
Anyone know how to sum by employee? 
 
EDIT: I'm making some test data which may help you and me. The test data works like it should but my real data does not. So posting test data will not help at this point. I'm not sure why my real data does not work.
3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @croberts21 ,

You can try the following dax:

Measure 2 =
var _id=
MAXX(
    FILTER(ALL('Employee'),'Employee'[firstname]=MAX('Employee'[firstname])),[Empid])
return
SUMX( FILTER(ALLSELECTED(JobCost),'JobCost'[Empid]=_id,'JobCost'[startDate])),[esthours])

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I put the measure on Jobcost and I got this error: 

croberts21_0-1692274112320.png

 

 

sergej_og
Super User
Super User

This is often the case when no valid relationship exists between tables (DimTable, FactTable).
Can you post a screen with your datamodel relationships of these two tables?
How is the field JobCost[estcost] defined?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors