![]() |
#2
|
|||
|
|||
![]()
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. |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot totals / subtotals | ProudLiberal | Excel | 0 | 03-20-2020 06:04 AM |
![]() |
excerbated123 | Excel Programming | 2 | 02-03-2015 09:28 PM |
![]() |
DavidW | Excel | 3 | 10-09-2014 07:08 AM |
Can I sort a Pivot table by subtotals? | oshkosh | Excel | 1 | 12-07-2010 06:20 PM |
![]() |
ninfanger | Excel | 3 | 10-23-2010 12:29 AM |