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.