Forum Discussion
Duplicate values removed from sum
Need your help BI wizards. I have a report for which an employees hours worked appear next to the associated date. This report also shows services rendered. So here's the rub. For a day like November...
You may just change the aggregation to Count and Maximum.
https://docs.microsoft.com/en-us/power-bi/service-aggregates
Yeah, I got that far. The max works but only for a day. For example 1 doctor on 1 day works just fine. BUT.... if you then slice for a team of doctors and / or a range of dates where a max value in the column would then have to sum, you still just get the highest max value :( Here is an example
| Doctor | Date of Service | Service Provided | Hours Worked | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Date | Count of Services | Hours worked for day | This works fine when just showing 1 doc on one day | ||
| Pete | 11/1/2017 | 5 | 9.5 | ||
| Doctor | Date of Service | Service Provided | Hours Worked | This is where it breaks if you show a team of docs or a range of dates | |
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| Pete | 11/1/2017 | checkup | 9.5 | ||
| James | 11/1/2017 | checkup | 8 | ||
| James | 11/1/2017 | checkup | 8 | ||
| James | 11/1/2017 | checkup | 8 | ||
| James | 11/1/2017 | checkup | 8 | ||
| James | 11/1/2017 | checkup | 8 | ||
| Date | Count of Services | Hours worked for day | |||
| 11/1/2017 | 10 | 9.5 | This is what I get | ||
| 17.5 | This is what I need |