Hi!
I'm struggling to create a formula that can give me a % result when filtered by any combination of three other columns.
Here is a mockup of my table:
| Location | Fiscal Year+Month+Name | SKU | Value |
| Country A | 2019-P08-Feb | Product A | 0 |
| Country A | 2019-P09-Mar | Product A | 3 |
| Country A | 2019-P10-Apr | Product A | 22 |
| Country A | 2019-P11-May | Product A | 2 |
| Country A | 2019-P12-Jun | Product A | 0 |
| Country A | 2020-P01-Jul | Product A | 0 |
| Country A | 2020-P02-Aug | Product A | 0 |
| Country A | 2020-P03-Sep | Product A | 0 |
| Country A | 2020-P04-Oct | Product A | 0 |
| Country A | 2020-P05-Nov | Product A | 0 |
| Country A | 2020-P06-Dec | Product A | 22 |
| Country A | 2020-P07-Jan | Product A | 13 |
| Country A | 2020-P08-Feb | Product A | 0 |
| Country A | 2020-P09-Mar | Product A | 0 |
| Country B | 2019-P08-Feb | Product A | 0 |
| Country B | 2019-P09-Mar | Product A | 0 |
| Country B | 2019-P10-Apr | Product A | 0 |
| Country B | 2019-P11-May | Product A | 0 |
| Country B | 2019-P12-Jun | Product A | 0 |
| Country B | 2020-P01-Jul | Product A | 0 |
| Country B | 2020-P02-Aug | Product A | 0 |
| Country B | 2020-P03-Sep | Product A | 0 |
| Country B | 2020-P04-Oct | Product A | 0 |
| Country B | 2020-P05-Nov | Product A | 0 |
| Country B | 2020-P06-Dec | Product A | 1 |
| Country B | 2020-P07-Jan | Product A | 0 |
| Country B | 2020-P08-Feb | Product A | 0 |
| Country B | 2020-P09-Mar | Product A | 0 |
| Country B | 2019-P08-Feb | Product B | 2 |
| Country B | 2019-P09-Mar | Product B | 4 |
| Country B | 2019-P10-Apr | Product B | 7 |
| Country B | 2019-P11-May | Product B | 9 |
| Country B | 2019-P12-Jun | Product B | 0 |
| Country B | 2020-P01-Jul | Product B | 0 |
| Country B | 2020-P02-Aug | Product B | 0 |
| Country B | 2020-P03-Sep | Product B | 0 |
| Country B | 2020-P04-Oct | Product B | 0 |
| Country B | 2020-P05-Nov | Product B | 0 |
| Country B | 2020-P06-Dec | Product B | 4 |
| Country B | 2020-P07-Jan | Product B | 0 |
| Country B | 2020-P08-Feb | Product B | 0 |
| Country B | 2020-P09-Mar | Product B | 0 |
And here are some examples of the results I want to formula to show:
| Country | Product | Month | No SKUs | No Locations | No. Months | No. of Values > 0 | No. of Values = 0 | % result: |
| A | A | ALL | 1 | 1 | 14 | 5 | 14 | 36% |
| B | A | ALL | 1 | 1 | 14 | 1 | 14 | 7% |
| A&B | A | ALL | 1 | 2 | 28 | 5 | 23 | 18% |
| B | A&B | ALL | 2 | 1 | 28 | 6 | 22 | 21% |
| A | A | 2019-P09-Mar | 1 | 1 | 1 | 1 | 0 | 100% |
| A | A | 2019-P08-Feb & 2019-P09-Mar | 1 | 1 | 2 | 1 | 1 | 50% |
| B | A&B | 2019-P10-Apr | 1 | 2 | 1 | 1 | 0 | 50% |
| A&B | A | 2020-P06-Dec & 2020-P07-Jan | 1 | 2 | 2 | 3 | 1 | 75% |
| A&B | A&B | 2020-P06-Dec & 2020-P07-Jan | 2 | 2 | 2 | 4 | 2 | 67% |
| B | A&B | 2020-P06-Dec & 2020-P07-Jan | 2 | 1 | 2 | 2 | 2 | 50% |
| ALL | ALL | ALL | 2 | 2 | 42 | 11 | 31 | 26% |
I feel like this should be simple but I've tried a few different things and so far no luck.
Do I need to create multiple formulae e..g one for Location and one for Month, or is it possible to do all in one?
Thank you!
