Formula Help

[hamzashafiq]

Hi Folks,

I've similar data below with 4 cases. Each case has the required Usage difference, Sum Cost and cost/hour numbers below. I've calculated the usage and cost for three cases with the condition (sum cost if hours <>0) however I'm left with the case when 0 came in between the distribution. For this we don't want to exclude the cost as 0 came in between. The numbers used to calculate the hours and sum cost are bolded. Can anyone help me to calculate cost for the 4th case?

Hours	Cost		Hours	Cost		Hours	Cost		Hours	Cost
10	20		0	20		10	20		10	20
30	30		30	30		30	30		0	30
40	40		40	40		40	40		40	40
50	50		50	50		50	50		50	50
60	60		60	60		0	60		60	60
Usage Difference	Cost Sum		Usage Difference	Cost Sum		Usage Difference	Cost Sum		Usage Difference	Cost Sum
50	200		30	180		40	140		50	200
cost/hour	4.00			6.00			3.50			4.00

[hamzashafiq]

Hi @v-henryk-mstf thanks for your reply, however it look like this formula will also include total cost for 2nd and 3rd case. We don't want to include the cost when the 0 hours came in the beginning or in the end. For those cases we won't include the total cost. For the 4th case when 0 appears in between so we will include the cost. I hope you understand.

[v-henryk-mstf]

Hi @hamzashafiq ,

In order to be able to achieve exactly what you want, can you provide screenshots of the desired results and describe them?

Looking forward to your reply.

Best Regards,
Henry

[hamzashafiq]

Hi @v-henryk-mstf on the table above in my first message, you can see Usage Difference and Cost Sum is the desired output we need from the bolded values in the column. The Usage difference is the difference of the bolded values above and total cost is the sum of the bolded one. I have bolded the values according to the case. I hope it helps.

[hamzashafiq]

Hi @v-henryk-mstf can you please help?

[v-henryk-mstf]

Hi @hamzashafiq ,

So your confusion is: for case 4, the sum value is not counted according to the requirement that the hour field is not equal to 0, right? It may be useful to refer to the following formula.

Cost Sum = CALCULATE(SUM('Table'[Cost]),'Table'[Hours]<>0)

Usage Difference = MAX('Table'[Hours])-MIN('Table'[Hours])

cost/hour = [Cost Sum]/[Usage Difference]

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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