Percentage of total across multiple dimensions

[Anonymous]

Good day,

I'm a new user and can't seem to find a solution searching the forums.

I have a pivot table with multiple dimensions and one expression.

I need to calculate a percentage of total for each category within a dimension; and following on this as a dimension is collapsed, it should provide a percentage of total for each category of the next dimension.

Using sum(Balance)/sum(total(Balance)) only gives a percentage of total of the full population across all dimensions.

I have attached an Excel file to explain with an example.

Would appreciate any assistance.

Regards,

Johan

[sunny_talwar · Accepted solution]

May be try this

=If(Max(Dimensionality()) = 3,

Sum(Balance)/Sum(TOTAL <[ORG 04 Description], Province> Balance),

If(Max(Dimensionality()) = 2,

Sum(Balance)/Sum(TOTAL <[ORG 04 Description]> Balance),

Sum(Balance)/Sum(TOTAL Balance)))

[Anonymous]

Hi Sunny,

Thank you so much, this has been very helpful and worked really well.

Kind regards,

Johan

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; not visible in preview).

If not, please make clear what part of this topic you still need help with .

[ranibosch]

How would you edit this expression if for example you would want to limit the results for KZN and GAUTENG only.

(set analysis to be on object)