Solved
Created two measures:
The first measure finds the Average hours for each store. My issue was that if a store had multiple surveys the pivot table would have multiple lines and thus give an inflated number, so this solves the issue for the individual store subtotals.
AvgFC:=AVERAGEX(Report,RELATED(Hours[FORECAST]))
Then, the second measure, which is the one the user sees, will use the AvgFC measure for individual store subtotals, but then SUM those averages for Division and Company totals.
Hours Forecasted:=IF(HASONEVALUE(Report[StoreNumber]),[AvgFC],SUMX (VALUES(Report[StoreNumber]),[AvgFC]))
HASONEVALUE- This returns true for Store subtotal lines, but false for higher levels (Division, Company), for those SUMX(VALUES( sums each AvgFC at the DivStore level.
|