Forum Discussion
Calculate difference using minimum values
I'm trying to establish a value for whether someone underused or overused hours allocated to a project. For instance, Tom Smith wasn't forcasted to work on 3/4/2024, so he was over $80.40. On 3/19 Nick Roth was forecasted to work 13 hours but actually worked 0.5, therefore underutilizing $314.88 of billable work.
My issue comes with a function from the raw data I cannot change. When someone has 0 approved hours, it defaults to an exhorborant number (again, something I cannot change). This is particulary challenging because if someone is forecasted to work and then doesn't, it's a missed opportunity to bill hours (see 3/11/2024).
My solution to this was to just use the minimum value in the Rate column per User. I tried making a separate calculated table with the User where it had their name and their minimum rate, which gave me a table with those values, but I was unable to calculate that Billing Delta column.
Any other ideas? Thank you!
Here's the table data.
| Date | Filter | Project | User | Rate | Forceasted hours | Actual hours | Forecasted Billing Amount | Actual billed amount | Billing Delta |
| 3/4/2024 | 1.00 | Tunnel Paint | Tom Smith | $ 20.10 | 0 | 4 | $80.40 | $80.40 | -$80.40 |
| 3/4/2024 | 1.00 | Tunnel Paint | George Green | $ 214,748,364.70 | 0 | 0 | $0.00 | $0.00 | $0.00 |
| 3/11/2024 | 1.00 | Tunnel Paint | Nick Roth | $ 214,748,364.70 | 10.88 | 0 | $0.00 | $0.00 | $2,336,462,207.94 |
| 3/11/2024 | 1.00 | Tunnel Paint | Tom Smith | $ 214,748,364.70 | 5.04 | 0 | $0.00 | $0.00 | $1,082,331,758.09 |
| 3/11/2024 | 1.00 | Tunnel Paint | George Green | $ 214,748,364.70 | 0 | 0 | $0.00 | $0.00 | $0.00 |
| 3/11/2024 | 1.00 | Tunnel Paint | Erin Erickson | $ 214,748,364.70 | 0 | 0 | $0.00 | $0.00 | $0.00 |
| 3/18/2024 | 1.00 | Tunnel Paint | Nick Roth | $ 24.60 | 13.3 | 0.5 | $12.30 | $12.30 | $314.88 |
| 3/18/2024 | 1.00 | Tunnel Paint | Tom Smith | $ 20.10 | 6.16 | 10 | $10.05 | $10.05 | -$77.18 |
| 3/25/2024 | 1.00 | Tunnel Paint | Nick Roth | $ 24.60 | 10.88 | 0.75 | $18.45 | $18.45 | $249.20 |
| 3/25/2024 | 1.00 | Tunnel Paint | Tom Smith | $ 214,748,364.70 | 5.04 | 0 | $0.00 | $0.00 | $1,082,331,758.09 |
| 10/28/2024 | 1.00 | Tunnel Paint | Nick Roth | $ 214,748,364.70 | 5.28 | 0 | $130.38 | $0.00 | $1,133,871,365.62 |
| 10/28/2024 | 1.00 | Tunnel Paint | Tom Smith | $ 214,748,364.70 | 0 | 0 | $0.00 | $0.00 | $0.00 |
| 11/4/2024 | 1.00 | Tunnel Paint | Nick Roth | $ 214,748,364.70 | 4.32 | 0 | $106.60 | $0.00 | $927,712,935.50 |
| 11/4/2024 | 1.00 | Tunnel Paint | Tom Smith | $ 214,748,364.70 | 0 | 0 | $0.00 | $0.00 | $0.00 |
| 11/11/2024 | 1.00 | Tunnel Paint | Nick Roth | $ 214,748,364.70 | 5.28 | 0 | $130.38 | $0.00 | $1,133,871,365.62 |
| 11/11/2024 | 1.00 | Tunnel Paint | Tom Smith | $ 214,748,364.70 | 0 | 0 | $0.00 | $0.00 | $0.00 |
| 11/11/2024 | 1.00 | Tunnel Paint | George Green | $ 214,748,364.70 | 0 | 0 | $0.00 | $0.00 | $0.00 |
Hi mikesdunbar ,
In Power BI Desktop, you can create calculated column to achieve thisActual Rate = CALCULATE( MIN('Table'[Rate]), ALLEXCEPT( 'Table', 'Table'[User] ) )Billing Delt Rate = ('Table'[Forceasted hours] - 'Table'[Actual hours]) * 'Table'[Actual Rate]Fina output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
some would say exorbitant, others would recognize a buffer overrun (2 to the Power of 31 minus 1, divided by 10 for some reason). Might want to replace that value with null.
Hi mikesdunbar ,
In Power BI Desktop, you can create calculated column to achieve thisActual Rate = CALCULATE( MIN('Table'[Rate]), ALLEXCEPT( 'Table', 'Table'[User] ) )Billing Delt Rate = ('Table'[Forceasted hours] - 'Table'[Actual hours]) * 'Table'[Actual Rate]Fina output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
In another column, show the expected result very clearly and also the method of calculation.
