Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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]
)
@Anonymous @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.
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]
)
@Blake753 , Try a measure like
sumx(summarize(Table, table[Employee],Table[Date], "_1", Average(Table[Hours])),[_1])
@Blake753 - Maybe:
Measure =
VAR __Table = SUMMARIZE('Table',[Employee],[Date],"Hours",AVERAGE([Hours]))
RETURN
SUMX(__Table,[Hours])
Seems to have worked, can you do a quick run through on how and why this worked?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |