Sum average using summarize function

[cheid]

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.

[danextian]

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]
)

[FBergamaschi]

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

[krishnakanth240 · Accepted solution]

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!

[Ashish_Mathur]

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.