Calculate difference using minimum values

[mikesdunbar]

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

[Ashish_Mathur]

Hi,

In another column, show the expected result very clearly and also the method of calculation.

[Anonymous · Accepted solution]

Hi @mikesdunbar ,
In Power BI Desktop, you can create calculated column to achieve this

Actual 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 He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

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.