Forum Discussion
Modelling data to have drill down capability
Hi all. I'm trying to set up a data model so that multiple subcategories roll up into an overall category and I am able to represent the overall category first in the visualization, then drill down to the subcategories. Would I need to set up a heirarchy in Power BI to achieve this?
| Location | Date | Subcategory 1 | Subcategory 2 | Subcategory 3 |
| New York | 1/1/2020 | 10 | 12 | 15 |
| Chicago | 2/1/2021 | 11 | 13 | 11 |
Hi , Anonymous
There is no need to add additional hierarchy.
But you need to unpivot your original data columns to generate two new columns .
Then drag these fields to column of matrix to achieve the drill-through function as below:
Check my sample file for more detais.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous the best practice would be to unpivot your data in Power Query and then create hierarchy in the Power BI.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi , Anonymous
There is no need to add additional hierarchy.
But you need to unpivot your original data columns to generate two new columns .
Then drag these fields to column of matrix to achieve the drill-through function as below:
Check my sample file for more detais.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
