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!

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

Hi cheid 

Use CALCUALTE to convert row context into filter context for  each aggregation

FILTER (
    ADDCOLUMNS (
        SUMMARIZE (
            STOPS,
            STOPS[Division],
            STOPS[Start of Week],
            STOPS[RouteID]
        ),
        "AVG TRC CNT",  CALCULATE ( AVERAGE ( STOPS[Longhaul Tractor Cnt] ) ),
        "AVG TRL CNT",  CALCULATE ( AVERAGE ( STOPS[Longhaul Trailer Cnt] ) ),
        "AVG TRC COST", CALCULATE ( AVERAGE ( STOPS[Longhaul Tractor Cost] ) ),
        "AVG TRL COST", CALCULATE ( AVERAGE ( STOPS[Longhaul Trailer Cost] ) )
    ),
    NOT ( ISBLANK ( [AVG TRC CNT] ) )
)

It is uncler though which column/virtual column (calcualated columns inside the table) you want to be summed but assuming you want it for "AVT TRC CNT", that would be 

SUMX (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                STOPS,
                STOPS[Division],
                STOPS[Start of Week],
                STOPS[RouteID]
            ),
            "AVG TRC CNT",  CALCULATE ( AVERAGE ( STOPS[Longhaul Tractor Cnt] ) ),
            "AVG TRL CNT",  CALCULATE ( AVERAGE ( STOPS[Longhaul Trailer Cnt] ) ),
            "AVG TRC COST", CALCULATE ( AVERAGE ( STOPS[Longhaul Tractor Cost] ) ),
            "AVG TRL COST", CALCULATE ( AVERAGE ( STOPS[Longhaul Trailer Cost] ) )
        ),
        NOT ( ISBLANK ( [AVG TRC CNT] ) )
    ),
    [AVG TRC CNT]
)