Column with % of another line in the same pivot table

[francisvandergr]

I have an pivot table. What i want in my second expression is a percentage of the line Turnover. How can i do this ? I include an example

[MayilVahanan]

Hi

Check the attached file. Hope that helps

[johnw]

sum(Amount)/sum({<Rubric={'Turnover'}>} total Amount)

[francisvandergr]

Thnx for your reaction

Sorry it is not what i want. I see at Turnover now of 57,14%

In the textbox you can see the values what i want  100%,10%,30% etc

[MayilVahanan]

Hi

Try this

sum(Amount) /sum(total if(Rubric='Turnover',Amount))

This give..

[johnw]

Mayil Vahanan Ramasamy wrote:

sum(Amount) /sum(total if(Rubric='Turnover',Amount))

This works on the surface, but would have two problems in practical applications:

1. If you select a specific Rubric other than Turnover, or make any other selections that result in the Turnover Rubric being excluded, then the percentage will no longer be calculated.
2. Performance.  A sum(if()) can be quite slow.

Set analysis avoids both of these problems.  For problem #1, set analysis FORCES the value of Rubric to be "selected" when calculating the sum.  For problem #2, set analysis is typically much faster than a sum(if()).

[MayilVahanan]

HI John Witherspoon

Thanks for your reply.

[francisvandergr]

Last question . What if i have a year as dimension, when i have selected more years i want the % of the turnover of the year. Now it works only when i have one year. I included a testfile again

[johnw · Accepted solution]

The "total" in the expression tells it to ignore all dimensions.  But you can explicitly tell it which dimensions to use.  In the below expression, we're telling it to ignore all dimensions but Jaar, so we should get the total turnover by Year.

sum(Amount)/sum({<Rubric={'Turnover'}>} total <Jaar> Amount)