Forum Discussion

croberts21's avatar
croberts21
Icon for Continued Contributor rankContinued Contributor
3 years ago

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.
Need Help

3 Replies

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    3 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

    • croberts21's avatar
      croberts21
      Icon for Continued Contributor rankContinued Contributor
      3 years ago

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