Forum Discussion
Create hierarchy with one column only
Hi everyone, i am using community for the very first time so if you don't understand my query please reply me back so i can let you know. I want to create hierarchy from one column. The image is attached below, as you can see there are different forms of revenue given below. I have create a table from visuals which include Total Revenue which i get by filtering the row it was simple. Now I want hierarchy when i click total revenue it gives drop down menu of revenue and then when i click revenue it give rest of the revenue's for exampe revenue normal work etc. Please share any strategy of how to do it or code.
Hi WaleedBinAli ,
Does your Expense column all end at a level in the Total row? If yes, you can try extracting the Total rows and then padding them up to get a hierarchy.
1.Add a conditional column to get the row contains 'Total'.
2.Fill up.
3.You can remove the 'Total' by adding a custom column.
4.Trim the column to remove the space.
Then the problem is that the first and last lines of each group are not needed, such as "Revenue" and "Total Revenue". The solution is to sort in groups and then delete the first and last lines, which I will not describe in detail here. You can view my attachments and go to Power Query to see the detailed steps.
This is the final result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Theres lots of ways to do Hierarchy in Power BI.
You can use Group, so on the column on the right pannel go to the column you want to group and right click and create group.
Another one is creating your logic using New Columns and you can add your formula like. IF's and Switch.Do you have an example for both which help me
Hi WaleedBinAli ,
Does your Expense column all end at a level in the Total row? If yes, you can try extracting the Total rows and then padding them up to get a hierarchy.
1.Add a conditional column to get the row contains 'Total'.
2.Fill up.
3.You can remove the 'Total' by adding a custom column.
4.Trim the column to remove the space.
Then the problem is that the first and last lines of each group are not needed, such as "Revenue" and "Total Revenue". The solution is to sort in groups and then delete the first and last lines, which I will not describe in detail here. You can view my attachments and go to Power Query to see the detailed steps.
This is the final result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks done with switch statements it was easy thanks alot
Thanks for answering ChristianDGreat. Will let you know brother.
