Forum Discussion
Sum average using summarize function
- 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!
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!