Set Expression

[Alam]

I have used the expression to calculate the % of column total where column in Month

sum([TPAM.total]) /sum(total <[TPAM.Month]>[TPAM.total])

My pivot:-

However, when I filter my pivot table based on any ID or account type, the % values change based on the selection

How do i edit this expression so that even if I have filtered for a specific ID, I still get a % value that represents % of entire column total, rather than % of selection total.

So, where it shows 99.48% in November for 'Others', it should show 0.14% (Total of November is 1,068,773 and total for this account type for this ID is 1,529 hence 1529/1068773 = 0.14%)

[ogster1974]

if you want to exclude a field from your set analysis 

{<ID= >}

will ignore any selections on this field.

[Alam]

@ogster1974 thank you. However, I have changed my expression to:-

sum([TPAM.total]) /sum(total {<[EIM ID-TrouxID]=>} <[TPAM.Month]> [TPAM.total] )

But it is still not working. 

Any suggestions please?

[vinieme12 · Accepted solution]

Ignore selections from both the numerator and the denominator

You most likely also need to ignore the second AccountType dimension as well

sum({<[EIM ID-TrouxID]=,AccountType=>}   [TPAM.total]) /sum({<[EIM ID-TrouxID]=,AccountType=>}  TOTAL  <[TPAM.Month]> [TPAM.total] )