#1
|
|||
|
|||
PowerPivot Subtotals
I have a pivot table, shown in the attached image. The pivot table is summarizing multiple types of data, and it is causing the totals to not calculate how I need it to. Each Store could have multiple surveys, so there may be multiple rows for each store, this is causing the "Forecasted Hours" to be added multiple times for stores that have multiple surveys. How can I have it only sum up each store once if they have multiple surveys. I can do an average to get each individual store to Subtotal correctly, but that does not fix the grand total.
|
#2
|
|||
|
|||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot totals / subtotals | ProudLiberal | Excel | 0 | 03-20-2020 06:04 AM |
Trying to find a way to calculate subtotals for different sections of an invoice w/ data validation | excerbated123 | Excel Programming | 2 | 02-03-2015 09:28 PM |
Multi Level Subtotals w 2 Grand Totals That Don't Match | 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 |
Using subtotals in Pivot table formulas | ninfanger | Excel | 3 | 10-23-2010 12:29 AM |