Sum of rows in pivot table

[Anonymous]

I've searched in this forum and haven't found my exact issue raised, wondering if this is possible: I have a pivot table which can be expanded at various nodes by the user and a Target column which is calculated based on existing data (does some rounding). They decide what level each Target needs to be calculated at, by expanding or collapsing dimensions. But I need the total rows to show the total of the lower level rows, not recalculate with rounding, as currently totals do not add up.

In the chart below, the total for Company 1 (and Division 1) should be 151, not 155. I played around with dimensionality() and aggr() but the formula will be different depending on what nodes have been expanded. Anyone know how I can do this?

[bc-thebruuu]

Am I totally nuts or ...

Sum(Aggr(Round(Sum(Count) *.9, 1), Zone))

Does the trick?

I know you ended up with an Excel based solution but Can you check as ... as you said, it shouldn't be that complicated?!

[Anonymous]

Thanks, but I don't want to aggregate at Zone level unless the pivot table is expanded to that level, and then only for the sections that have expanded to Zone. Others should be aggregated at the level expanded to. It may work with my limited sample data set, but not with a full set of production data where there are thousands of records. The rounding should only be done at the level expanded for each section. The totals should simply sum what is being displayed on the screen.

[Anonymous]

Eg your calc (New Total) works when the table is expanded. But when collapsed to Division it gives the incorrect number (it is calculating at Zone then adding together). It needs to apply the % reduction at the level expanded to, not Zone level. If that makes sense?

The number in the Target field changes due to rounding, depending on what is collapsed and expanded. If I expand Division 1 to Zone level, I will get a total of 205, and your number is correct. But I need the Target expression calculated at the level expanded to (as it is currently doing, with no aggr), just need the Total to reflect the sum of that Target expression. Don't think this is possible.