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 1st rob worked 8 hours. However, there may be multiple rows for November first because there were 3 separate services rendered. So... Instead of showing just 8 hours worked in the sum it shows 24. I need to calculate the sum of hours worked for that day without the duplicate / redundant rows adding
11/1/2017 service a 8
11/1/2017 service b 8
11/1/2017 service c 8
date service count hours worked
11/1/2017 3 8
3 Replies
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 I created a calculated field in tableau that did the job. Just have to do it in BI. Below is my tableau field
{ FIXED [Date of Service], [Provider Name]:MIN([Hours])}
