cancel
Showing results for
Did you mean:
Helper II

## Sum duplicate data only once per date measure

I have a dataset that is curretnly structured like this with some values repeating for the same date:

 Employee name Date Hours Hank 8/26/2020 7 Hank 8/26/2020 7 Hank 8/25/2020 5.5 Sarah 8/26/2020 9 Sarah 8/26/2020 9 Sarah 8/25/2020 8

I am trying to create a measure that sums the hours based on Unique Name and Date values.  For instance, the total for Hank here would be 12.5 (7+5.5) and 17 for Sarah (9+8).  I need the rows with duplicate dates to only be counted once towards the sum.  I cannot go in and delete that duplicate row either, it is needed for other purposes.  What is the best way to go about doing this?

1 ACCEPTED SOLUTION
Solution Sage

Here's probably the most simple way to calculate it. Maybe even easy to understand and most likely the fastest...

``````[Total] =
SUMX(
SUMMARIZE(
T,
T[Employee Name],
T[Date],
T[Hours]
),
T[Hours]
)``````

5 REPLIES 5
Helper II

@daxer @amitchandak @Greg_Deckler I have the same question but I am looking to do a distinct count of employee names.  So instead of sum up the hours I need to count names.  My results from the orginal question would be 2 for Hank and 2 for Sarah.

Solution Sage

Here's probably the most simple way to calculate it. Maybe even easy to understand and most likely the fastest...

``````[Total] =
SUMX(
SUMMARIZE(
T,
T[Employee Name],
T[Date],
T[Hours]
),
T[Hours]
)``````

Super User IV

@Blake753 , Try a measure like

sumx(summarize(Table, table[Employee],Table[Date], "_1", Average(Table[Hours])),[_1])

Proud to be a Super User!

Super User IV

@Blake753 - Maybe:

``````Measure =
VAR __Table = SUMMARIZE('Table',[Employee],[Date],"Hours",AVERAGE([Hours]))
RETURN
SUMX(__Table,[Hours])``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Helper II

Seems to have worked, can you do a quick run through on how and why this worked?

Announcements