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.