Forum Discussion
how to remove duplicates before computing sum
I have a table like the following:
| Week Key | Week Label | Employee Key | Employee Name | Employee Location | Day Worked | Hours Worked for Day | Hours Worked for Week |
| 1 | Week 1 | 1 | John Doe | Florida | Monday | 8 | 40 |
| 1 | Week 1 | 1 | John Doe | Florida | Tuesday | 8 | 40 |
| 1 | Week 1 | 1 | John Doe | Florida | Wednesday | 8 | 40 |
| 1 | Week 1 | 1 | John Doe | Florida | Thursday | 8 | 40 |
| 1 | Week 1 | 1 | John Doe | Florida | Friday | 8 | 40 |
| 1 | Week 1 | 2 | Jane Doe | California | Tuesday | 5 | 30 |
| 1 | Week 1 | 2 | Jane Doe | California | Thursday | 8 | 30 |
As you can see, the grain of this table is week + employee + day worked. I want a measure that returns the distinct [Hours Worked for Week] value per week + employee. So, for example, for week 1 + employee 1, I expect this measure to return 40 (not 40*5 = 200). Similarly, for week 1 + employee 2, I expect this measure to return 30 (not 30*2 = 60). How can I achieve this? To be sure, as shown in the data, [Hours Worked for Week] is not always the sum of [Hours Worked for Day]. So, adding up the values in [Hours Worked for Day] is an incorrect solution.
Anonymous , try like
sum Hours Worked for Week = sumx(summarize(Table,Table[Employee Key], Table[Week Key], "_1",max(Table[Hours Worked for Week])),[_1])
2 Replies
- Ashish_Mathur
Super User
Hi,
Drag Week label and Employee key to the table visual and write this measure
=MIN(Data[Hours Worked for Week])
Hope this helps.
- amitchandak
Anonymous , try like
sum Hours Worked for Week = sumx(summarize(Table,Table[Employee Key], Table[Week Key], "_1",max(Table[Hours Worked for Week])),[_1])
