Creating a Bar Graph with Main Groups and Their Sub Groups

[Bridgewater]

Hi all,

I need to calculate total sales figures for each main sales group. One main sales group has three sub groups within it. I need to show each sub groups numbers as well as the main group's total within one bar chart. 

Is this possible?

Here is a data sample. In theory, the bar graph should show Group 3 with the most sales (1260) with subgroup 3.3 in second, etc.

I have an issue when grouping the groups and subgroups, the graph looks messy.

Group     Sales

1             100

2             200

3             300

3.1          310 <-- subgroup

3.2          320 <-- subgroup

3.3          330 <-- subgroup

Thank you

[v-huizhn-msft]

Hi @Bridgewater,

I create the sample table in my Power Bi desktop, and get expected result, please review the following steps.

1. Create a new calculated column to display the Gruop, the original group shows the sub-group.

Group = LEFT(Table6[Sub-Group],1)

2. Create a Stacked column chart, select the Group as Axis, the sub-group as Legend, Sales as value. You will get the following result.


Best Regards,
Angelia

[Bridgewater]

 @v-huizhn-msft Thank you so much for that help! 

Separating the Main Groups and Sub Groups into different colums helped.

One more question. Is it possible to do what you did, but also have the subgroups as their own bar? Below is what I'm trying to acheive ultimately. This was done in excel and transfered to a word doc but it is way too time consuming, hence why I'm trying to use PBI for this. In this example, the dollar numbers aren't necessarily important, only that Group 2 is the combined value of Sub Group 2.1, 2.2, and 2.3. Those subgroups also have their own bar.

Thank you again

[v-huizhn-msft]

Hi @Bridgewater,

Yes, you can create a clustered bar chart and select the sub-group as X-axis, the sales as value, please see more details in the screenshot below.



Best Regards,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @Bridgewater,

If your problem is answered appropriately, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia