cancel
Showing results for
Search instead for
Did you mean:  Helper III

## Calculate cost per mile by Total Cost for each given year

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 1 ACCEPTED SOLUTION  Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3  Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.  Helper III

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])``````  Community Support

HI @mrmiyagi,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks #### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp! Top Solution Authors
Top Kudoed Authors
Users online (3,155)