Calculations containing set analysis in Pivot table

melwilson — Wed, 25 Nov 2020 16:16:04 GMT

Hello.

I am having a problem with set analysis in a pivot table.

I have created a small table with two out of many KPIs:

KPI Amount

A 150

B 280

Total 430

Using KPI as a dimension and the expression:

and then clicking on showing partial sum to get Total at the bottom.

I am using another expression to try to get another column to show the amount for both A and B as a percentage of another KPI.

When I try to calculate it as a percentage of another KPI I am having problems. I want to try and get this:

KPI Amount Percentage

A 150 3.7%

B 280 7%

Total 430

However, its is coming out as

KPI Amount

A 150

B 280

Total 430 10.8%

The expression I am using is:

=num(sum({<KPI = {,} >} Amount)

sum({<KPI = {} >} Amount),

'#,#%')

How do I use set analysis to get the percentages against each KPI instead of only as a total ?

Thanks for any help you can suggest.

Mel

Re: Calculations containing set analysis in Pivot table

sunny_talwar — Wed, 10 Oct 2018 13:52:03 GMT

Try this

=Num(Sum({<KPI = {,} >} Amount)

Sum(TOTAL {<KPI = {} >} Amount),

'#,#%')

melwilson — Thu, 11 Oct 2018 09:06:15 GMT

Hi, Sunny.

That is perfect.

Thankyou for responding so quickly. It has really helped me out.

Mel.