Pivot table % Totals

[#1]

Hi all,

I have a pivot table as shown below. there is an expression which calculates the % which is working per line but the subtotals and totals are not working. (As you can see it shows 33.33% for each.

Does anyone know how i can fix this please?

The expressions used are ..

Score

Sum(Score)

Percentage

=sum(Score)/sum({<City=>}(aggr(NODISTINCT sum( Score), Country)))

[#2]

Hi

Try this:

=sum(Score)/sum(TOTAL<City> aggr(sum(Score),Continent,Country, City))

TMF

Message was edited by: tmendesf

[#3]

Hi TMF,

That works for the totals but now each line reports as 100% as below

[#4]

Hi

Try this:

=sum(Score)/sum(TOTAL<City> aggr(sum(Score),Continent,Country))

TMF

[#5]

Hi, thanks again but now im getting some lines reporting nothing..

Nottingham for example should be reporting 20/35*100 =  57.14% but is showing -

[#6]

Can you post a sample file?

[#7]

Hi, yes here is a sample

[Gysbert_Wassenaar · Accepted solution]

Try:

if(Dimensionality()=3, sum(Score)/sum(total <Country,Continent> Score),

if(Dimensionality()=2, sum(Score)/sum(total <Continent> Score),

sum(Score)/sum(total Score)))