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 order.  

 

 

These counts and cost need to rollup to division level that is mapped back to the invoice table.  To rollup everthing to the division level in the stops table I used the below DAX logic.  The problem I am having is that I need to sum the average of each average.  Is there away to sum the average using summarize?    I tried using SUMX, but it's not working.  

 

Longhaul Rates =
 FILTER(
     SUMMARIZE(        
        STOPS,
        STOPS[Division],
        STOPS[Start of Week],
        STOPS[RouteID],
        "AVG TRC CNT",AVERAGE(STOPS[Longhaul Tractor Cnt]),
        "AVG TRL CNT",AVERAGE(STOPS[Longhaul Trailer Cnt]),
        "AVG TRC COST",AVERAGE(STOPS[Longhaul Tractor Cost]),
        "AVG TRL COST",AVERAGE(STOPS[Longhaul Trailer Cost])
     ),
     NOT(ISBLANK([AVG TRC CNT]
     )
     )
)
 
 
This is what I want the final table to look like.

 

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!

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 months ago

    Hi,

    I cannot understand your question.  Share data in a format that can be pasted in an MS Excel file.  Show the expected result clearly.

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    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
    Super 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]
)