Hi,
I am trying to create a horizontal bar chart that I can drill into. I have the following data set in Excel.
| Company Details 1 | Company Details 2 | Company Details 3 | Company Details 4 | Company Details 5 | Company Details 6 | Speed Sub category 1 | Speed Sub category 2 | Speed Sub category 3 | Balance Sub Category 1 | Balance Sub Category 2 | Balance Sub Category 3 | Balance Sub Category 4 | Balance Sub Category 5 | Balance Sub Category 6 | Comment |
| Test company | Test location | Test name | Test position | Test number | Test name | 1 | 1 | 5 | 5 | 1 | 5 | 5 | 2 | 3 | Test response |
| Test company 1 | Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | Test response 1 |
Initially on the bar chart I want to show the average of the Speed and average of Balance scores as two separate bars. I then want to be able to drill down into both bars separatley. For example I want to be able to double click on Speed see the three individual bars relating to each three Speed Sub Categories.
When I have tried to do this in Power BI it doesn't let me create a hierarchy. I tried adding two new columns in my data to show the average for Speed and Balance but still couldnt create a hierarchy.
Someone suggested I unpivot the columns to change the layout of the data, so I ended up with all questions in the same column and the scores to the right, as per below.
| Company Details 2 | Company Details 3 | Company Details 4 | Company Details 5 | Company Details 6 | Attribute | Value | Comment |
| Test location | Test name | Test position | Test number | Test name | Speed Sub category 1 | 1 | Test response |
| Test location | Test name | Test position | Test number | Test name | Speed Sub category 2 | 1 | Test response |
| Test location | Test name | Test position | Test number | Test name | Speed Sub category 3 | 5 | Test response |
| Test location | Test name | Test position | Test number | Test name | Balance Sub Category 1 | 5 | Test response |
| Test location | Test name | Test position | Test number | Test name | Balance Sub Category 2 | 1 | Test response |
| Test location | Test name | Test position | Test number | Test name | Balance Sub Category 3 | 5 | Test response |
| Test location | Test name | Test position | Test number | Test name | Balance Sub Category 4 | 5 | Test response |
| Test location | Test name | Test position | Test number | Test name | Balance Sub Category 5 | 2 | Test response |
| Test location | Test name | Test position | Test number | Test name | Balance Sub Category 6 | 3 | Test response |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Speed Sub category 1 | 5 | Test response 1 |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Speed Sub category 2 | 5 | Test response 1 |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Speed Sub category 3 | 5 | Test response 1 |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Balance Sub Category 1 | 5 | Test response 1 |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Balance Sub Category 2 | 5 | Test response 1 |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Balance Sub Category 3 | 5 | Test response 1 |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Balance Sub Category 4 | 5 | Test response 1 |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Balance Sub Category 5 | 5 | Test response 1 |
| Test location 1 | Test name 1 | Test position 1 | Test number 1 | Test name 1 | Balance Sub Category 6 | 5 | Test response 1 |
As the Sub Categories were all in one column I couldnt see how I could create the hierarchy to be able to drill down.
Does anyone have any ideas how I can fix this?
Thanks!
