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?

[Anonymous]

Thanks Lisa, aggr() needs to pass the dimensions, I don't know what they will be as it's based upon the user expanding or collapsing the dimensions. For one total row, I'd need to identify what dimensions were expanded or collapsed in the records before it, and calculate at those dimensions then add up. Aggr() would work great if the pivot table was expanded consistently for all rows. But maybe there's something in aggr() that I'm missing?

[vishsaggi]

Sure. Sounds like a plan.

[Anonymous]

I've attached a test app to my post. If you expand as per the image below, I need the Company 1 and Division 1 totals to show 205, not 204. Will be forever grateful if this can be achieved!!

[Anonymous]

But if I collapse the Division dimension so that Region is not shown, the total should be 204. Total needs to be dynamically calculated as the sum of the lowest levels expanded.

[vishsaggi]

Not sure if this is even possible dynamically, but using Dimensionality() it should work. I will ask if stalwar1‌ has any ideas on this.

[sunny_talwar]

This looks very interesting. Checking it....

[vishsaggi]

Thanks Sunny I am interested too to know how it can be achieved.

[sunny_talwar]

Not entirely there... but I guess one step closer

[vishsaggi]

This looks awesome. I would have never thought of using GetObjectField in Aggr(). Let us see what Karen has to say.

[sunny_talwar]

It is still not there yet... the total for Company 1 is also needed to be 205 and the total of the Group would be 282.. if I understood the requirement correctly.