Calculating Operating Cost of Equipment

[tjshaf13]

I am trying to calculate the total operational cost for individual pieces of equipment.

I have the cost/hour for each piece of equipment and the number of hours each piece of equipment has operated/day. 

This information is coming from two different data sets, but is associated via the "Equipment Number" field. 

I know is a simple matter of multiplication, but I can't get a measure to work. I am relatively new to Powerbi so there is probably something obvious that I am missing. Thanks for you help in advance

Here are pictures of my data sets

Daily Operating HoursOperating Cost/hour

[v-yiruan-msft]

Hi @tjshaf13 ,

First, please make sure there is one relationship based on equipment number be created between these two tables. Then create a measure as below to get the operation cost of per equipment.

Operating Cost of Equipment = var _cEquip=max('Operating Costs'[Equipment Number])
var _totalHour=CALCULATE(SUM('Daily Operating Hours'[TotalHoursCrushing]),FILTER('Daily Operating Hours','Daily Operating Hours'[EquipmentNumber]=_cEquip))
var _cost=CALCULATE(MAX('Operating Costs'[Cost]),FILTER('Operating Costs','Operating Costs'[Equipment Number]=_cEquip))
return _totalHour*_cost

Best Regards

Rena

[Ashish_Mathur]

Hi,

If the Daily Operating hours table is not very large, then you may write a calculated column formula to bring over the Cost field from the Operating Cost/hour table by using this formula: =RELATED('Operating Cost/hour[Cost]).  You must first build a relationship from the Equipment Number column of the Operating Cost/hour table to the EquipmentNumber column of the Daily Operating hours table.  Lastly, write this measure

=SUMX('Daily Operating hours','Daily Operating hours'[TotalHoursCrushing]*'Daily Operating hours'[Cost])

Hope this helps.

[mahoneypat]

Assuming your "Equipment" table (2nd one) has a 1:Many with the "Hours" table with Hours.  Make a table visual with the Equipment Number from the Equipment table and use a measure like this.

Total Cost = var costperhour = Sum(Equipment[Cost] //since one value per equipment, other aggregation would work (max, min)

var hours = sum(Hours[TotalHoursCrushing]

return costperhour*hours

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat