cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrmiyagi
Helper III
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

 

CategoryMiles
Natural Surface201.0
Hard Surface68.6

 

costpermile.PNG

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
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.

View solution in original post

 

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

powerbi_cpm.PNG

 

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

Sum_Size = SUM('Attributes'[Size])

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

 

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

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