topic Re: Pivot table sums are incorrect in App Development

Pivot table sums are incorrect

khadijaceesay — Thu, 31 Oct 2024 14:17:55 GMT

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?

Re: Pivot table sums are incorrect

khadijaceesay — Fri, 01 Nov 2024 14:48:12 GMT

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))

Re: Pivot table sums are incorrect

seanbruton — Fri, 01 Nov 2024 15:42:47 GMT

Superb !!! Neat Solution.