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.
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
You may just change the aggregation to Count and Maximum.
https://docs.microsoft.com/en-us/power-bi/service-aggregates
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])}
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 |
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 |
---|---|
15 | |
3 | |
1 | |
1 | |
1 |
User | Count |
---|---|
26 | |
3 | |
2 | |
2 | |
2 |