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?
Not easily, not without removing sensitive client data. Is there something specific in the data you need to see? I can try to explain it.
There is a 7 level hierarchy from Group down to Location and data is stored at the Location level. The formula is using a sum of a specific integer field, and then applying a percentage reduction, then rounding that result. Hence the number will be different at different levels due to the rounding.
I have a feeling this isn't possible. The formula is calculating correctly but I want the totals to just sum those rounded amounts, rather than recalculate. Not possible with a pivot table?
Just follow these instructions in below link to scramble sensitive data and upload.
Sample app would help us to look into which saves time.
This is quite a complex app with a large data model and section access. If I get a chance tonight I'll mock up a simple app with an inline table with the levels and data as I've described above.
Help does show the Aggr function as a solution. Sound like you have already found that and it still doesn't work ?