Return Average of a Column from a Table Generated by a Measure

[RickBickens]

Hi there,

I have a measure which creates a table in which I need the average of one of the columns. I am generating the table in a measure so the whole table can be re-evaluated when certain parameters are changed. I am having trouble calculating the average of the last column of the table and returning that value.
The measure is as follows:

Measure First Shift Util = 
var HrsPerShift = HoursPerShift[HoursPerShift Value]
var DesignLinesPerHour = 'SummarizedDays'[Design Lines per Hour]
var table1 = ADDCOLUMNS(ADDCOLUMNS(ADDCOLUMNS(SUMMARIZE(Order_Data,Order_Data[FIXED_DATE], "Lines", COUNTROWS(Order_Data)), "Hours_Required", [Lines] / DesignLinesPerHour), "Shifts_Required", ROUNDUP([Hours_Required]/HrsPerShift,0)),"First_Shift_Utilization", If([Shifts_Required] = 1, [Lines]/(HrsPerShift*DesignLinesPerHour),1))

return AVERAGE([First_Shift_Utilization])

I have other measures that filter the data for days when there are 2 shifts per day, 3 shifts per day, etc. as well that I will need to return the average of the utilization column created of the table created by the measure. 

I've tried using AVERAGEX instead of AVERAGE (example below) but do not get the correct value when I do so due to how AVERAGEX calculates. 

Measure First Shift Util = 
var HrsPerShift = HoursPerShift[HoursPerShift Value]
var DesignLinesPerHour = 'SummarizedDays'[Design Lines per Hour]
var table1 = ADDCOLUMNS(ADDCOLUMNS(ADDCOLUMNS(SUMMARIZE(Order_Data,Order_Data[FIXED_DATE], "Lines", COUNTROWS(Order_Data)), "Hours_Required", [Lines] / DesignLinesPerHour), "Shifts_Required", ROUNDUP([Hours_Required]/HrsPerShift,0)),"First_Shift_Utilization", If([Shifts_Required] = 1, [Lines]/(HrsPerShift*DesignLinesPerHour),1))

return AVERAGEX(table1,[First_Shift_Utilization])

I used these measures to create tables to compare against, so I know the table creation part of the measure is populating how I expect, my issue is just returning the average of the "First_Shift_Utilization" column.

Thanks!
 

[Greg_Deckler · Accepted solution]

@RickBickens If table1 returns the correct values per row, then AVERAGEX should work. What are you getting versus what you expect? Can you post sample source data? Do you perhaps need to FILTER your table1 variable in your AVERAGEX?

[RickBickens]

@Greg_Deckler Thanks for confirming AVERAGEX is the way to go to achieve this. I dug a bit more and have realized that it was the effect of one of my "Filters on all Pages" that was causing the discrepancy.

[Greg_Deckler]

@RickBickens Sounds good. In the future you can use CONCATENATEX in place of your AVERAGEX or similar X aggregator to view your intermediary table and this may help you troubleshoot.