Forum Discussion

cheid's avatar
cheid
Frequent Visitor
8 months ago
Solved

Sum average using summarize function

I was given the below table that I mapped to a line item table by the routeid column using the related function. Because I am using a line item table there are multiple lines of counts and cost per o...
Need Help
  • krishnakanth240's avatar
    krishnakanth240
    8 months ago

    Hi cheid 

     

    Build the Route-level averages (virtual table)
    VAR RouteAvgTable =
    SUMMARIZE(
    STOPS,
    STOPS[Division],
    STOPS[Start of Week],
    STOPS[RouteID],
    "Avg_Tractor_Cnt", AVERAGE(STOPS[Longhaul Tractor Cnt]),
    "Avg_Trailer_Cnt", AVERAGE(STOPS[Longhaul Trailer Cnt]),
    "Avg_Tractor_Cost", AVERAGE(STOPS[Longhaul Tractor Cost]),
    "Avg_Trailer_Cost", AVERAGE(STOPS[Longhaul Trailer Cost])
    )


    Sum those averages to Division level
    Allocated Tractor Count =
    SUMX(
    RouteAvgTable,
    [Avg_Tractor_Cnt]
    )

     

    Allocated Trailer Count =
    SUMX(
    RouteAvgTable,
    [Avg_Trailer_Cnt]
    )

     

    Allocated Tractor Cost =
    SUMX(
    RouteAvgTable,
    [Avg_Tractor_Cost]
    )

     

    Allocated Trailer Cost =
    SUMX(
    RouteAvgTable,
    [Avg_Trailer_Cost]
    )

     

    If this doesn't works out, then please share the sample data with the required logic and outout. Thank You!

FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper User
8 months ago

Your question is unclear 

 

Please specify the issue clearly and usig pictures that help identify what the issue is and what is you want to get

 

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI