Calculating Cost Per Mile (CPM) by Range of Utilization

[ascott]

Power BI - Can this be done with DAX?

Calculating Cost Per Mile (CPM)
CPM = Cost/Miles

Table contains information about maintenance costs for motor vehicles.
UniqueAssetId: A unique identifier that never changes
AssetNbr: The number painted on the side of the vehicle. This can change.
Cost: Repair costs charged to the vehicle.
Utilization: The life-to-date mileage on the vehicle at the time of the repair.
UniqueAssetId AssetNbr Cost Utilization
17471 4047 $2.38 364446
17471 4047 $2.80 364446
17471 4047 $6.47 364446
17471 4047 $6.85 362739
17471 4047 $10.98 362739
17471 4047 $12.49 362739
17471 4047 $17.64 364018
17471 1247 $64.38 287956
17471 1247 $42.83 287956
17475 3501 $125.00 299065
17475 3501 $50.00 315546
17475 3501 $26.00 350043
17475 3501 $23.35 381025
17475 3501 $15.25 384525
17478 4054 $16.38 358148
17478 4054 $12.49 358148
17478 4054 $35.00 357931
17478 4054 $39.38 357931
17478 4054 $51.54 358539

Visualizations:
1. Life-To-Date CPM per UniqueAssetId
2. Life-To-Date CPM all vehicles
3. CPM 0-100,000 miles per UniqueAssetId, CPM 100,001 - 200,000 miles per UniqueAssetId, ...
4. CPM 0-100,000 miles all vehicles, CPM 100,000 - 200,000 miles all vehicles, ...

Notes: In lines 3 & 4 above only include costs that occured in the specified mileage range.
In lines 3 & 4 above only include miles traveled in the mileage range:

Example: UniqueAssetId 17475 has a Current Utilization of 384,525, this means the vehicle has traveled:

100,000 miles in the 0 - 100,000 range with a cost of $0.00 so CPM = 0
100,000 miles in the 100,000 - 200,000 range with a cost of $0.00 so CPM = 0
100,000 miles in the 200,000 - 300,000 range with a cost of $125.00 so CPM = $0.0000125
84,525 miles in the 300,000 - 400,000 range with a cost of $114.60 so CPM = $0.0013558118899734

[Eric_Zhang · Accepted solution]

---

@ascott wrote:

Power BI - Can this be done with DAX?

Calculating Cost Per Mile (CPM)
CPM = Cost/Miles

Table contains information about maintenance costs for motor vehicles.
UniqueAssetId: A unique identifier that never changes
AssetNbr: The number painted on the side of the vehicle. This can change.
Cost: Repair costs charged to the vehicle.
Utilization: The life-to-date mileage on the vehicle at the time of the repair.
UniqueAssetId AssetNbr Cost Utilization
17471 4047 $2.38 364446
17471 4047 $2.80 364446
17471 4047 $6.47 364446
17471 4047 $6.85 362739
17471 4047 $10.98 362739
17471 4047 $12.49 362739
17471 4047 $17.64 364018
17471 1247 $64.38 287956
17471 1247 $42.83 287956
17475 3501 $125.00 299065
17475 3501 $50.00 315546
17475 3501 $26.00 350043
17475 3501 $23.35 381025
17475 3501 $15.25 384525
17478 4054 $16.38 358148
17478 4054 $12.49 358148
17478 4054 $35.00 357931
17478 4054 $39.38 357931
17478 4054 $51.54 358539

Visualizations:
1. Life-To-Date CPM per UniqueAssetId
2. Life-To-Date CPM all vehicles
3. CPM 0-100,000 miles per UniqueAssetId, CPM 100,001 - 200,000 miles per UniqueAssetId, ...
4. CPM 0-100,000 miles all vehicles, CPM 100,000 - 200,000 miles all vehicles, ...

Notes: In lines 3 & 4 above only include costs that occured in the specified mileage range.
In lines 3 & 4 above only include miles traveled in the mileage range:

Example: UniqueAssetId 17475 has a Current Utilization of 384,525, this means the vehicle has traveled:

100,000 miles in the 0 - 100,000 range with a cost of $0.00 so CPM = 0
100,000 miles in the 100,000 - 200,000 range with a cost of $0.00 so CPM = 0
100,000 miles in the 200,000 - 300,000 range with a cost of $125.00 so CPM = $0.0000125
84,525 miles in the 300,000 - 400,000 range with a cost of $114.60 so CPM = $0.0013558118899734

---

@ascott

You can try to create a calculated table and calculate columns(CONNECT_COL) as below

ranges = 
CROSSJOIN (
    DISTINCT ( yourTable[UniqueAssetId] ),
    DATATABLE (
        "range", STRING,
        {
            { "0-100000" },
            { "100000-200000" },
            { "200000-300000" },
            { "300000-400000" },
            { "400000-500000" }
        }
    )
)

CONNECT_COL = CONCATENATE('ranges'[UniqueAssetId],"@"&'ranges'[range])

Then connect the calculated table with your table through the calculated column.

d

After that, create a measure as

cost per mile = 
VAR CPM =
    DIVIDE ( SUM ( yourTable[Cost] ), 100000 )
VAR CPM_InLast100000 =
    DIVIDE (
        SUM ( yourTable[Cost] ),
        MOD ( MAX ( yourTable[Utilization] ), 100000 )
    )
VAR CPM_To_Return =
    IF (
        MAX ( ranges[range] )
            = CALCULATE (
                MAX ( yourTable[range] ),
                ALLEXCEPT ( ranges, ranges[UniqueAssetId] )
            ),
        CPM_InLast100000,
        CPM
    )
RETURN
    IF (
        MAX ( ranges[range] )
            <= CALCULATE (
                MAX ( yourTable[range] ),
                ALLEXCEPT ( ranges, ranges[UniqueAssetId] )
            )
            && ISBLANK ( CPM_To_Return ),
        0,
        CPM_To_Return
    )

See more in the attached pbix file.

[ascott]

Eric, That was great information and a completely different way than I was doing the Cost Per Mile Ranges. This works great! I have another issue I wonder if you can assist with. Is there a way in the same table to get the Total Cost Per Mile for each column with out the DAX simply adding up all the individual calculations or does it have to be a separate table? We need to grab all the vehicles in that range and calculate the total cost, the total Utilization for that range and come up with the CPM for each range. In the example you used in the cpm.zip file it shows vehicle 1247 has a range utilization of 87956 for the 200000-300000 range and vehicle 3501 having a utilization of 84525 for the same range. If we add those utilization's up we get 172481 for a total of that range. The total cost for the same vehicles and same range is $346.81. If we calculate that CPM it comes out to .0020107142235956 but if we let the Matrix show the totals you get .0023221 as the range CPM. If I have to use another table but nest it underneath the existing Matrix I am fine with that but I am having trouble getting that information as well with the Matrix being long and cumbersome. I basically just need the total CPM for each range for all vehicles. Thanks for any assistance in advance. You have been a great help!

[parry2k]

@ascott all this can be done, all you looking for full solution or you have a quesiton any specific issue which you are unable to solve it.

Your post doesn't say if you tried to do something and you ran into issue, and need help witht that.

[ascott]

Ok so we are able to figure out the MAX easy enough and I am able to get a life to data MAX and Total cost in order to figure out a LTD CPM. 

However, when we try to break to range utilization down and then figure the cost for just that same range. We are unable to get those numbers so we can get the correct CPM for each range. 

We are creating this for vehicles. In the end we want to know what each vehicles (asset) Total Cost is for each range of utilization, The utilization within that range and then use that to figure out the Cost Per Mile by each of those ranges.