Calculate cost per mile by Total Cost for each given year

[Anonymous]

Hello! I am trying to create a measure that can calculate the cost per mile for each type of category from the total cost (labor + materials) for each given year. How would this be possible? Thanks in advance

Category	Miles
Natural Surface	201.0
Hard Surface	68.6

[v-shex-msft · Accepted solution]

Don'@mrmiyagi,

You can try using the following formal calculation table to dsiplay summary results:

Detail Table =
ADDCOLUMNS (
    SUMMARIZE (
        ADDCOLUMNS ( ALLSELECTED ( Table ), "Year", YEAR ( [Date] ) ),
        [Year],
        [Asset Category],
        "Count", COUNT ( Table[Asset Category] ),
        "T_labor", SUM ( Table[Labor Cost] ),
        "T_Materials", SUM ( Table[Labol_Materials] )
    ),
    "Cost_Per_Miles",
        DIVIDE (
            [T_labor] + [T_Materials],
            [Count]
                * SWITCH ( [Asset Category], "Natural Surface", 201.0, "Hard Surface", 68.6 )
        )
)

Best regards
Xiaoxin Sheng

[Anonymous]

Thanks for the response! I already have a table 'Attributes' which has the mileage and will be further expanded with more classes. I tried it a different way but it seems like something is missing as I should get the same amount if I mulitply Size x CPM (cost per mile) but thats not the case. Is this because I need to include some time intelligence? 

Selecting one year yeilds 465.32 CPM for "Natural Surface Trail" 

465.32 x 201 = 93,529.32

The total sum for Natural Surface Trail is 125,405.04

SumLabor_Materials = SUM('Booked Labor'[Labor Cost]) + SUM('Booked Materials'[Material Cost])

Sum_Size = SUM('Attributes'[Size])

CPM = DIVIDE([SumLabor_Materials],[Sum_Size])

[v-shex-msft]

HI @Anonymous,

It sounds like a common multiple aggregates calculations issue on the total level, you can take a look at Greg's blog to know more about how to solve this:

Measure Totals, The Final Word 
Regards,

Xiaoxin Sheng