Hello!!
I need to edit a matrix table, but the data is kind of awkward so I'm having some trouble.
The data is divided like in this mock table below:
| KPI | Year | Value |
| Apple | 2022 | 10 |
| Pear | 2021 | 9 |
| Grape | 2022 | 25 |
| Fruits | 2022 | 70 |
| Green grape | 2022 | 11 |
| Purple grape | 2022 | 14 |
| Lettuce | 2021 | 2 |
| Tomato | 2022 | 5 |
| Salad | 2021 | 14 |
| Iceberg lettuce | 2021 | 1 |
| Apple | 2021 | 17 |
| Pear | 2022 | 22 |
| Grape | 2021 | 40 |
| Fruits | 2021 | 100 |
| Green grape | 2021 | 15 |
| Purple grape | 2021 | 25 |
| Lettuce | 2022 | 5 |
| Tomato | 2021 | 7 |
| Salad | 2022 | 30 |
| Iceberg lettuce | 2022 | 3 |
I have a lot of levels of data, but they aren't categorized per se. In the mock table the levels are fruits > grape > purple grape. And all the data in under one column called KPI, in this example.
In the end, I want the matrix to look like this:
| KPI | 2021 | 2022 |
| Fruits | 100 | 70 |
| Grape | 40 | 25 |
| Green grape | 15 | 11 |
| Purple grape | 25 | 14 |
| Pear | 9 | 22 |
| Apple | 17 | 10 |
| Salad | 14 | 30 |
| Lettuce | 2 | 5 |
| Iceberg lettuce | 1 | 3 |
| Tomato | 7 | 5 |
Where some KPI have lower hierarchy then others and I have to be able to put them in a chosen order. Like, Fruits have to be in the first line, followed by Grapes, then by Apples and last by Pear.
I can't create subcolumns to categorize them because I would have to create at least 20 columns, since I need them in a specific order and I have much more data then the example.
Is this doable? If not, is there any other way I can achieve this?
Thank you so much for your guys help!!
