Pivot Table Sum

[mphekin12]

Can anyone please help out and tell me why the column titled 'Mix Builder' isn't summing correctly?  Every other column in the pivot table is correct except for this one.

Thank you for your help!

[Jason_Michaelides]

Are you expecting the total to be a sum of all the rows?  That's not how pivot tables work - the total row is calculated independently over the grouped dataset. The result may well not be the same as the sum of all the rows.

[#3]

Hi

hard to say without explanations.

What should it show ?

What is the expression ?

Chris

[mphekin12]

Sorry for the confusion.  Here is another screen shot with more information.

So if you look at Group 1 Sales, Group 1 Cost, Group 1 Profit, Group 2 Sales, Group 2 Cost, or Group 2 Profit at a division level, all of the Total lines are correct.  If you look at the total line for Mix Builder it isn't.  I guess my question is why does the total work over all columns of data but not the Mix Builder?

Thanks!

[Jason_Michaelides]

What is the Mix Builder expression?

[mphekin12]

Jason,

Thanks for taking the time to look this over.  Here is the formula:

(sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} TOTAL<D.Country, D.Area, D.District, D.Division> Price_Ext)) *

(round(sum({$<Year=Group2::Year, Month=Group2::Month, Quarter=Group2::Quarter, Date=Group2::Date>} Price_Ext) / sum({$<Year=Group2::Year, Month=Group2::Month, Quarter=Group2::Quarter, Date=Group2::Date>} TOTAL<D.Country, D.Area, D.District, D.Division> Price_Ext), 0.00001)) *

(round(sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Profit) / sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Price_Ext), 0.00001))

[mphekin12]

Jason,

One more thing that may help answer this issue.  I have a fast change on the object.  When I view the data as a Straight table, the total is correct.

Thanks!

[Jason_Michaelides]

How does this expression differ from the others?

[mphekin12]

This expression isn't much different from the others.  The other columns actually build the Mix Builder column.  When I look at each segment of the formula individually it works correctly, but as a whole the total doesn't match.  I have verified that each individual line is correct, it is just the total that isn't.

[mphekin12 · Accepted solution]

After a lot of research (especially a post called 'Total Sum in Pivot Table') I figured out that I needed to wrap each segment of my formula in an aggr() function and then sum() the whole thing.  For anyone interested the following formula gives me my desired outcome:

sum(

  aggr((sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} TOTAL<D.Country, D.Area, D.District, D.Division> Price_Ext)), D.Country, D.Area, D.District, D.Division, [Major Line])

  *

  (round(

   aggr((sum({$<Year=Group2::Year, Month=Group2::Month, Quarter=Group2::Quarter, Date=Group2::Date>} Price_Ext)), D.Country, D.Area, D.District, D.Division, [Major Line])

   /

   aggr((sum({$<Year=Group2::Year, Month=Group2::Month, Quarter=Group2::Quarter, Date=Group2::Date>} TOTAL<D.Country, D.Area, D.District, D.Division> Price_Ext)), D.Country, D.Area, D.District, D.Division, [Major Line])

   , 0.00001)

  )

  *

  (round(

   aggr((sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Profit)), D.Country, D.Area, D.District, D.Division, [Major Line])

   /

   aggr((sum({$<Year=Group1::Year, Month=Group1::Month, Quarter=Group1::Quarter, Date=Group1::Date>} Price_Ext)), D.Country, D.Area, D.District, D.Division, [Major Line])

   , 0.00001)

  )

  )

Thanks to all that helped out!