Power BI Calculate different values for related records

0
votes

In Power BI I have bunch of different types of pumps, I pay a variable daily rate for each pump depending on these rules: For each day: If the pump is "On Duty" then full rate. If the 1st pump of a given type "On Standby" then $1.00 per day. If 2nd or more of the same type "On Standby" then 1/2 daily rate. If "Out of Service" then no cost.

I have a daily record for each pump with: Date, Pump Type, Status. And a related table with: Pump Type, Daily Cost

Can anyone suggest a method to approaching this calculation

1
powerbidax
can you show us some data, it will be easy to give some hints and it should not be difficult to create what you expect – AnkUser

1 Answers

0
votes

You can create metrics like these:

OnDutyPumpTotal = CALCULATE(sum(YOUR_DATASET[FullRate]),FILTER(YOUR_DATASET,YOUR_DATASET[PumpType] = "On Duty"))

OnStandbyFirstPumpTotal= CALCULATE(sum(1),FILTER(YOUR_DATASET,YOUR_DATASET[PumpType] = "On Standby"),FILTER(YOUR_DATASET,YOUR_DATASET[isFirstPump] = "Yes"))

OnStandbyNonFirstPumpTotal= CALCULATE(sum(YOUR_DATASET[FullRate])/2,FILTER(YOUR_DATASET,YOUR_DATASET[PumpType] = "On Standby"),FILTER(YOUR_DATASET,YOUR_DATASET[isFirstPump] = "No"))

OutofServiceTotal= CALCULATE(sum(0),FILTER(YOUR_DATASET,YOUR_DATASET[PumpType] = "Out of Service"))

If you want to build charts, then you can create a dynamic table like that:

Pump Dataset = UNION (ROW ( "PumpType", "On Duty", "Day", VALUE(YOUR_DATASET[OnDutyPumpTotal])), ROW ( "PumpType", "Stand by First Pump", "Day", VALUE(YOUR_DATASET[OnStandbyFirstPumpTotal])), ROW ( "PumpType", "Stand by Non First Pump", "Day", VALUE(YOUR_DATASET[OnStandbyNonFirstPumpTotal])), ROW ( "PumpType", "Out of Service", "Day", VALUE(YOUR_DATASET[OutofServiceTotal])) )

Replace the "YOUR_DATASE" to your dataset name and fields.