Piviot Table % of Total

[#1]

I have been struggling for some time with a pivot table. The table shows a variety of dimensions and has only one expression, which is the current balance of a customer account. My problem is that I am trying to incorporate a column that shows the percent of each customers balance in relation to the total of the previous expression. Every formula I have tried simple returns 100%. I think this is because it is simple dividing the customers balance by itself rather than the total balance of all customers. Does anyone know how to do this?

[johnw]

What do you mean by "the total of the previous expression"?

If customer is your fourth dimension, and you want the % to that customer's balance compared to all customers in the group given by the previous three dimensions, I think this would work:

sum(Balance)/sum(total <YourDim1,YourDim2,YourDim3> Balance)

[#3]

I need to divide the total expression balance by the individual customer balances. The first expression is sum(balance) and i need to make a second express that take the balance for each customer and divides it by the total for all customers (which is found in the first expression). I hope that makes more sense its hard for me to explain it.

[blaise]

sum(Balance) / sum(all Balance) ??

Post an example and i will have a look at it, if the sum(Balance) / sum(all Balance) don't do the trick.

[#5]

The above formula did not work. This is the formula I am currently working with.

sum({$< BankDateSequence={$(=Max(BankDateSequence))},[Participating Bank]-={'XYZ'}>} [Current Balance])

/

sum(total<ParticipationBankName,[Customer Account],[Customer Name]>[Current Balance])

I have three dimensions before this formula that are all customer related. Then I have an expression giving me the current balance by customer. I am trying to get what % each customers makes of the total balance. Its almost like if i was making a pie chart and chose to make it relative rather than display the $ amounts. Any help you can give me would be greatly appreciated!

[#6 · Accepted solution]

I answered my own question with the help of the people above, so I thought I would post the solution in case anyone else is struggling with the same problem.

sum({$< BankDateSequence={$(=Max(BankDateSequence))},[Participating Bank]-={'ABC'}>} [Current Balance])

/

sum({$< BankDateSequence={$(=Max(BankDateSequence))},[Participating Bank]-={'ABC'}>} total<[Customer Account]> [Current Balance])