topic Re: Duplicate values removed from sum in Developer

Duplicate values removed from sum

Robmartinshp — Fri, 26 Jan 2018 06:14:40 GMT

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

Re: Duplicate values removed from sum

v-chuncz-msft — Thu, 25 Jan 2018 04:53:32 GMT

@Robmartinshp,

You may just change the aggregation to Count and Maximum.

https://docs.microsoft.com/en-us/power-bi/service-aggregates

Re: Duplicate values removed from sum

Robmartinshp — Thu, 25 Jan 2018 17:07:35 GMT

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

Re: Duplicate values removed from sum

Robmartinshp — Thu, 25 Jan 2018 20:24:10 GMT

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])}