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.

Attached Images

Screenshot 2023-06-13 152136.png (221.0 KB, 4 views)

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.