I have the data in the following format. I need a dynamic hierarchy which decomposition tree gives me and then I need two values one would be the distinct count of the key a each level of the hierarchy i.e. If I start with the model I need the distinct count of the key for the model selected on the decomposition tree. If model is the first level of teh hierarchy then i will have A,B,C and when A is selected on the decomposition tree then I need the count of the key for the model A. After this when I go to the next level as series and select 45. I need the distinct count of the key for Model A and series 45 and so on.
The second value would be to get the distinct count of the similar keys present at the hierarchy level i.e. if I select the model as teh fisrst hierarchy and select A then the distinct count of similar key would be 1 since only the key abc is present in all the three series, similary when I go down the hierarchy I need the count to change so for example if i look at model A series 45 it has two verticals hence the distinct count of the similar items is 1 which is bcd.
Once these two values are calculated, I need to have these values on the bar graph along with the decomposition tree such that, when I select the level 1 hierarchy I need the corresponding values of level 1 and when I select level 2 on decomposition tree I need the values of level 1 to remain same and alsp have the level 2 hierarchy beside it for comparison.
Any help on how to achieve this would be appreciated. Also the bars of the decomposition tree should be based on the this
Currently these are the formulas that I am using to get the values
UN_Numerator (Distinct count of similar items) =
VAR A = CALCULATE(DISTINCTCOUNT(Table[Key],KEEPFILTERS(Table))
VAR B = CALCULATE(DISTINCTCOUNT(Table[Key]),
CALCULATETABLE(
FILTER(ADDCOLUMNS(Table,"ABC",COUNTROWS(FILTER(Table,EARLIER(Table[Key])=Tavle[Key]))),[ABC]=A)))
RETURN B
UN_Denominator (distinct count of all the items at the hierarchy) = CALCULATE(DISTINCTCOUNT(Table[Key]),KEEPFILTERS([Table]))
| Model | Series | Vertical | Key |
| A | 45 | 87 | abc |
| A | 45 | 87 | bcd |
| A | 45 | 98 | bcd |
| A | 14 | 56 | esd |
| A | 14 | 56 | efd |
| A | 14 | 56 | fgd |
| A | 14 | 56 | bgr |
| A | 23 | 17 | abc |
| A | 23 | 17 | cde |
| A | 23 | 17 | ert |
| A | 23 | 17 | yhf |
| A | 23 | 17 | gfd |
| B | 14 | 26 | abc |
| B | 14 | 26 | abc |
| B | 14 | 26 | bcd |
| B | 14 | 26 | cde |
| B | 45 | 54 | esd |
| B | 45 | 54 | abc |
| B | 45 | 54 | fgd |
| B | 45 | 54 | bgr |
| C | 23 | 10 | abc |
| C | 23 | 10 | cde |
| C | 14 | 20 | ert |
| C | 14 | 20 | yhf |
| C | 14 | 20 | gfd |
