total invariant to filters in a pivot table-

[KKahina]

hi,

I want to make the total for a pivot table invariant to filters, i tried the overall expression, but i don't know how to di it and where exactly

i have two dimension and one measure:

dimension 1: [Agence] in rows

dimension 2: [Vague] in columns

measure:   sum({<[Code du sondage]={'DEFENT'},[Q206]-={'',98,99}>}([Q206]*Poids))
/sum({<[Code du sondage]={'DEFENT'},[Q206]-={'',98,99}>}Poids)

I want the total be invariant to  dimension 1, meaning I want the total of my measure for all " Agence"

thank you in advance for your help

[treysmithdev]

You can use the TOTAL keyword to do this.

sum(TOTAL <Vague> {<[Code du sondage]={'DEFENT'},[Q206]-={'',98,99}>}([Q206]*Poids))
/sum(TOTAL <Vague> {<[Code du sondage]={'DEFENT'},[Q206]-={'',98,99}>}Poids)

TOTAL will give you the total without being grouped by the dimensions, except those fields listed between < >. So this should give you Sum by Vague for TOTAL Agence.

[KKahina]

Thank you for your response @treysmithdev 

The thing is I don't know where exactly to put that expression, i enabled the totals for the first dimension ( Agence)  that gave the pivot table below, and i don't found a way to edit the expression to make it invariant to filters as explained

[treysmithdev]

If you are only wanting this in the total row, you will have to use Pick or If, which will make it slower.

If(RowNo() = 0, sum(TOTAL <Vague> {<[Code du sondage]={'DEFENT'},[Q206]-={'',98,99}>}([Q206]*Poids))
/sum(TOTAL <Vague> {<[Code du sondage]={'DEFENT'},[Q206]-={'',98,99}>}Poids), sum({<[Code du sondage]={'DEFENT'},[Q206]-={'',98,99}>}([Q206]*Poids))
/sum({<[Code du sondage]={'DEFENT'},[Q206]-={'',98,99}>}Poids))

[KKahina]

thank you for quick response @treysmithdev 

it still don't work, i put the expression in the "afficher la colonne si" field in the second  capture i attached in my previous reply.  The total is still variant to filters