Forum Discussion
basic excel formula in power bi
I am trying to find a count of missing shifts. Assume 3 shifts/day, totaling 24 hours. basic excel formula below is 24 - billable hours, the next cell is /8 to get a count of how many shifts were missing.
| Post Description | Day | Billable Hours | 24-c2 | d2/8 |
| Mobile Patrol Unit | 1 | 24 | 0 | 0 |
| Mobile Patrol Unit | 2 | 24 | 0 | 0 |
| Mobile Patrol Unit | 3 | 16 | 8 | 1 |
| Mobile Patrol Unit | 4 | 20 | 4 | 0.5 |
| Mobile Patrol Unit | 5 | 16 | 8 | 1 |
| Mobile Patrol Unit | 6 | 16 | 8 | 1 |
| Mobile Patrol Unit | 7 | 16 | 8 | 1 |
| Mobile Patrol Unit | 8 | 24 | 0 | 0 |
| Mobile Patrol Unit | 9 | 25.25 | -1.25 | -0.15625 |
| Mobile Patrol Unit | 10 | 16 | 8 | 1 |
| Mobile Patrol Unit | 11 | 16 | 8 | 1 |
| Mobile Patrol Unit | 12 | 16 | 8 | 1 |
| Mobile Patrol Unit | 13 | 16 | 8 | 1 |
| Mobile Patrol Unit | 14 | 16 | 8 | 1 |
| Mobile Patrol Unit | 15 | 24 | 0 | 0 |
| Mobile Patrol Unit | 16 | 24 | 0 | 0 |
| Mobile Patrol Unit | 17 | 24 | 0 | 0 |
| Mobile Patrol Unit | 18 | 24 | 0 | 0 |
| Mobile Patrol Unit | 19 | 24 | 0 | 0 |
| Mobile Patrol Unit | 20 | 24 | 0 | 0 |
| Mobile Patrol Unit | 21 | 24.75 | -0.75 | -0.09375 |
| Mobile Patrol Unit | 22 | 24 | 0 | 0 |
| Mobile Patrol Unit | 23 | 24 | 0 | 0 |
| Mobile Patrol Unit | 24 | 24 | 0 | 0 |
| Mobile Patrol Unit | 25 | 24 | 0 | 0 |
| Mobile Patrol Unit | 26 | 23.75 | 0.25 | 0.03125 |
| Mobile Patrol Unit | 27 | 24 | 0 | 0 |
| Mobile Patrol Unit | 28 | 24 | 0 | 0 |
| Mobile Patrol Unit | 29 | 23 | 1 | 0.125 |
| Mobile Patrol Unit | 30 | 16 | 8 | 1 |
| Mobile Patrol Unit | 31 | 13 | 11 | 1.375 |
Hi raicardi ,
to adjust the total try this measure
SUMX(
VALUES(Sheet1[Work Date]),
(24 - SUMX(Sheet1[Billable Hours])) /8
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
what's the expected output? The 2 columns you showed in the sample data should be easy to get. The formula is the same as excel.
this is what is returned. The desired outcome is the same as the excel format outlined above. basically looking to see which days there were not 3 filled shifts to grab a total of how many were missing that day.
this gave the desired outcome, but how do i fix the total to sum the column??
Hi raicardi ,
to adjust the total try this measure
SUMX(
VALUES(Sheet1[Work Date]),
(24 - SUMX(Sheet1[Billable Hours])) /8
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi raicardi ,
try to write 24 - [Billable Hours] without any reference to the table
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
