Pivot table sums are incorrect

[khadijaceesay]

I created a measure with the expression below and added it to a pivot table:

if (Index(concat(CorrDimNum),'d')>0,
if(LocationType = 'S' ,
sum(CorrSqFeetShipped),
Sum(CorrGoodSqFeetProduced)
),Sum({$<[LocationType]-={'S'}>}CorrGoodSqFeetProduced))

For some reason the subtotal rows when I collapse the pivot table are not properly summing the rows within the section. Total shows 90,340,989 instead of 96,900,981. Any idea how I can fix this?

[khadijaceesay · Accepted solution]

I solved this issue by wrapping my measure expression in a sum(aggr() function and listing all possible categories for the visual. New formula for measure is below.

sum(aggr((if (Index(concat(CorrDimNum),'d')>0,
if(LocationType = 'S' ,
sum(CorrSqFeetShipped),
Sum(CorrGoodSqFeetProduced)),
Sum({$<[LocationType]-={'S'}>}CorrGoodSqFeetProduced))),SalesRegionName,SalesAreaName,PlantName,CorrWorkWeek))

[seanbruton]

Superb !!! Neat Solution.