Sum of values that have been duplicated

[Js112233]

Hello there! 

I have an odd problem that I can't seem to wrap my mind around. I have a table formatted like this:

ID	Employee	Hours	Cause		Materials	Material Hours
F544.070	Employee 1	1	Fire		Truck 1	1
F544.070	Employee 1	1	Fire		Truck 2	1
F544.070	Employee 2	2	Fire		Truck 3	1
F544.070	Employee 2	2	Fire		Truck 4	1
F544.070	Employee 3	1	Fire		Truck 5	1
F544.070	Employee 3	1	Fire		Truck 6	1
F544.070	Employee 4	2	Fire		Truck 7	1
F544.070	Employee 4	2	Fire		Truck 8	1
F544.070	Employee 5	3	Fire		Truck 9	1
F544.070	Employee 5	1	Fire		Truck 10	1
F544.070	Employee 6	1	Fire		Truck 11	1
F544.070	Employee 6	1	Fire		Truck 12	1

This report is used to track employees, hours spent on the job, what job they responded to, and the materials used on the job. Unfortunately there are ALWAYS more materials corresponding to a jon (ID) number so it formats like the about, i.e. creating more information.  It can be duplicates, triplicates, etc etc depending on how many materials are used on the job.

So when I want to sum the hours for the employee on a particular job it is not correct. Is there a way to get a distinct sum based off of the ID number?

[Phil_Seamark]

Hi there,

Can you please provide some more info.  I take it you've tried a simple SUM(table[Material Hours]) and this is giving you a number higher than expected?  From your screenshot this might produce a value of 12 for the job with the ID of F544.070 ?

What number should be shown in this case?

[Js112233]

It should show 12 for the materials however the employee hours are not accurate. For example there should only be entry per employee for an ID, so Employee 1 should have a sum of 1 hour on the job. Instead, Employee 1 has two.

[Phil_Seamark]

Is there anyway you can post a copy of a cut down PBIX file here?  

It might be good to understand the tables and how they relate to better answer your question.

[Js112233]

Hey there, thanks for taking the time to reply. Here is a quick copy paste of what the table looks like:

ID	Material	Number_of_Materials	Cost	Employee	Hours
F123.44	Pipe	7	$1	Employee 1	7
F123.44	Rock	2	$1	Employee 1	7
F123.44	Sand	2	$1	Employee 1	7
F123.44	Dump Truck	3	$1	Employee 1	7
F123.44	Compressor	1	$1	Employee 1	7
F123.44	Truck2	1	$1	Employee 1	7
F123.44	Excavator	1	$1	Employee 1	7
F123.44	Truck3	1	$1	Employee 1	7
F123.44	Truck4	1	$1	Employee 1	7
F123.44	Concrete	24	$1	Employee 1	7
F123.44	Dirt	5	$1	Employee 1	7
F123.44	Lane Fee	1	$1	Employee 1	7
F123.44	Rock2	7	$1	Employee 2	7
F123.44	Pipe	2	$1	Employee 2	7
F123.44	Rock	2	$1	Employee 2	7
F123.44	Sand	3	$1	Employee 2	7
F123.44	Dump Truck	1	$1	Employee 2	7
F123.44	Compressor	1	$1	Employee 2	7
F123.44	Truck2	1	$1	Employee 2	7
F123.44	Excavator	1	$1	Employee 2	7
F123.44	Truck3	1	$1	Employee 2	7
F123.44	Truck4	24	$1	Employee 2	7
F123.44	Concrete	5	$1	Employee 2	7
F123.44	Dirt	1	$1	Employee 2	7
F123.44	Lane Fee	7	$1	Employee 3	7
F123.44	Rock2	2	$1	Employee 3	7
F123.44	Pipe	2	$1	Employee 3	7
F123.44	Rock	3	$1	Employee 3	7
F123.44	Sand	1	$1	Employee 3	7
F123.44	Dump Truck	1	$1	Employee 3	7
F123.44	Compressor	1	$1	Employee 3	7
F123.44	Truck2	1	$1	Employee 3	7
F123.44	Excavator	1	$1	Employee 3	7
F123.44	Truck3	24	$1	Employee 3	7
F123.44	Truck4	5	$1	Employee 3	7
F123.44	Concrete	1	$1	Employee 3	7

So going to the employee column you can see that employee 1 looks to have worked many more hours than he actually did. The employee only gets one entry per unique job ID. The materials column has so much more in it that it pushes everything down with it. Employees 1, 2 & 3 all only worked 7 hours on the job.

[Phil_Seamark]

You could create a new Table using DAX that summarises the data down to 1 line per employee/job using this

Employee Job Hours = SUMMARIZECOLUMNS('Table'[ID],'Table'[Employee],"Hours",MAX('Table'[Hours]))

Does that help?

[Js112233]

This would have to be a new table rather than a measure within the existing?

[Phil_Seamark]

This measure could be added to your table that contains the duplicate hours and return the single value for each employee

MyMeasure = CALCULATE(MAX('Table'[Hours]),ALLEXCEPT('Table','Table'[Employee]))

Might need to add job in there but would need more data/info to be sure.

[Js112233]

Hey I think we are on to something with this! If I were to add the job ID in there where would it go?

I am a total newbie, thanks for bearing with me.

[Phil_Seamark]

MyMeasure = CALCULATE(MAX('Table'[Hours]),ALLEXCEPT('Table','Table'[Employee],'Table'[ID]))

[Js112233]

Well, when I create a new table with your first suggestion the values are correct! However, with this latest one it is not returning the correct sum.

[Phil_Seamark]

Can you post a screenshot of what it is showing and let me know what it should be?

[Js112233]

It is not summing correctly. It will have the correct hours but in the total the number will be wrong. For example have correct hours for seven jobs that show one hour each. Total should say 7 but says 13 instead.

[Js112233]

Bumping to see if anyone has any other ideas

[Phil_Seamark]

Hey there, I am still thinking about this.  

Is there anyway you can share a copy of your PBIX file (or stripped version of it) and I'm sure I can resolve for you.

Phil

[Js112233]

Let me try to throw something together that I can share, it is a large file with a TON of other information within.