Pivot Tables Filterations

To explain my problem, I use the below sample database.

INDIVIDUAL_ID DATE DRINKS(L) BRAND GENDER AGE_GROUP
1 20230509 100 A F 1
1 20230509 150 B F 1
2 20230509 200 D M 2
2 20230509 120 E M 2
2 20230509 90 A M 2
3 20230509 220 B F 3

I want to create a Pivot table as follows

BRAND Sum of DRINKS Consumption %
A 190 21.6
B 370 42.0
D 200 22.7
E 120 13.6

When calculating the consumption%, it should calculate based on the total consumption based on the GENDER and AGE_GROUP selection.

User should be able to filter the Pivot table from any number of BRANDs as well.

Eventhough user filter from BRAND, consumption% should needs to calculate based on the GENDER & AGE_GROUP selections only.

Can anybody help to get the Consumtion% for my requirement please.

1 Like

Hi @PandaDH

Are you saying that you would like the denominator to ignore filters for brand but accept filters for Gender/age group when calculating consumption %?

thanks

Hi @Ramon_Lopez yes that is exactly what is my question.

Hi @Ramon_Lopez, really appreciate if you can help me here…

hi @PandaDH

One option would be to use LAC-W function with ‘pre-filter’ as your parameter. this means that the formula will be computed prior to filtering the data and could give you what you are looking for. Please check out this blog post which covers some examples.

thanks!
Ramon

1 Like