totals in rows of a pivot table

[ali_hijazi]

Hello
got a pivot table with an expression that is giving the below result:

what I want in the second column is to have under AMER 365 and under APAC  473 which are the totals

of my expression in the first column and thus I can calculate the % and cumulative %
4 / 365
2/365

...
and under APAC
2/473
3/473

...
my expression is a simple count

[marcus_sommer · Accepted solution]

You need to specify all dimensions within the total <Dim1, Dim2, ...> which should be consider (seems to be the horizontal ones) within the aggregation. Further for a simple count you don't need an aggr() and could skip this part.

[ali_hijazi]

Hello Marcus
didn't work
it is displaying the whole total (as if putting total without  including any dimension in <> )
but works if I select AMER or APAC one at a time

[ali_hijazi]

what I want is to aggr to the dimension just before the one not expanded
but don't know how to accomplish this

[marcus_sommer]

Probably there are more things included within your expression and it might be helpful to comment everything which isn't related to the total-logic. This means to use:

count(distinct Field)
   and
count(distinct total <Dim1, Dim2, ...> Field)

afterwards you may add step by step any of the set analysis again. Like mentioned an aggr() shouldn't be necessary for this - but if you the aggr() you may need to set there and/or within any outer aggregation also an appropriate set analysis as well as using there also a total statement.