I have two tables with similar granularities and i want to calculate a simple KPI that divides one measure by another in the second table. the two tables are linked together by country, business type dimension, and date dimension. The KPI is simply dividing the total Sales of the first by the total Vol for each month. However i want this measure to always be done at country level and does not change if I select shop or store from the customer type dimension.
| Date | BusinessType | Country | Sales |
| 01/01/2023 | Shop | ES | 100.00 |
| 02/01/2023 | Shop | ES | 200.00 |
| 03/01/2023 | Shop | ES | 300.00 |
| 01/01/2023 | Shop | ES | 500.00 |
| 02/01/2023 | Shop | ES | 600.00 |
| 03/01/2023 | Shop | ES | 700.00 |
| Date | BusinessType | Country | Vol |
| 01/01/2023 | Store | ES | 1000 |
| 02/01/2023 | Store | ES | 1200 |
| 03/01/2023 | Store | ES | 1300 |
| 01/01/2023 | Store | ES | 1000 |
| 02/01/2023 | Store | ES | 1200 |
| 03/01/2023 | Store | ES | 1300 |
When I test in a table my table looks like this, which is correct at total. But I want this measure to be impacted no matter what i select in Business Type
| Date | BusinessType | Sales | Vol | Percentage |
| 01/01/2023 | Shop | 600.00 | | 0 |
| 01/01/2023 | Store | | 2,000.00 | |
| Total | | 600.00 | 2,000.00 | 30% |
However when I select a Business Type from the slicer as Shop for example this is what I see.
| Date | BusinessType | Sales | Vol | Percentage |
| 01/01/2023 | Shop | 600.00 | | 0 |
| Total | | | | 0% |
Thank you!
