I have a hierarchy and need to calculate shares.
| GeographyHierarchy | Sales |
| Nation | 103 |
| OM | 45 |
| OMA | 6 |
| OMA01 | 1 |
| OMA02 | 2 |
| OMA03 | 3 |
| OMB | |
| OMB01 | |
| OMB02 | |
| OMB03 | |
| OMC | |
| OMC01 | |
| OMC02 | |
| OMC03 | |
| ON | |
| ONA | |
| ONA01 | |
| ONA02 | |
| ONA03 | |
| ONB | |
| ONB01 | |
| ONB02 | |
| ONB03 | |
| ONC | |
| ONC01 | |
| ONC02 | |
| ONC03 | |
| Territory | District | Region | Nation | Sales |
| OMA01 | OMA | OM | Nation | 1 |
| OMA02 | OMA | OM | Nation | 2 |
| OMA03 | OMA | OM | Nation | 3 |
| OMB01 | OMB | OM | Nation | 4 |
| OMB02 | OMB | OM | Nation | 5 |
| OMB03 | OMB | OM | Nation | 6 |
| OMC01 | OMC | OM | Nation | 7 |
| OMC02 | OMC | OM | Nation | 8 |
| OMC03 | OMC | OM | Nation | 9 |
| ONA01 | ONA | ON | Nation | 10 |
| ONA02 | ONA | ON | Nation | 5 |
| ONA03 | ONA | ON | Nation | 6 |
| ONB01 | ONB | ON | Nation | 7 |
| ONB02 | ONB | ON | Nation | 8 |
| ONB03 | ONB | ON | Nation | 9 |
| ONC01 | ONC | ON | Nation | 10 |
| ONC02 | ONC | ON | Nation | 1 |
| ONC03 | ONC | ON | Nation | 2 |
Using Geography Hierarchy as dimension, I want to calculate shares in terr, district and region. For eg. share of territory OMA01 = Sales of OMA01 / Sales of District OMA
Share of district OM = Sales of OM / Sales of Rgion OM
Share of Region OM = Sales of OM / Sales of Nation
I am able to achieve this by If - else condition however it doesn't work in set analysis.For territory shares, I am using
Sum({<District = {'$(=Left(GeographyHierarchy,3))' } >}Sales). This works on a single selection though.
PS : Geography Hierarchy is associated with Terr, District, Region and nation accurately. I.e. Nation under geo hierarchy has all territories, districts, region and Nation value against it and so on.
