Thread: [Solved] PowerPivot Subtotals
View Single Post
 
Old 06-15-2023, 12:31 PM
schilling3003 schilling3003 is offline Windows 10 Office 2010 64bit
Novice
 
Join Date: Feb 2016
Posts: 3
schilling3003 is on a distinguished road
Default 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.
Reply With Quote