Grouping two columns with same data (for a stacked column chart)

[PatrickH]

Hello everyone,

I am new to Power BI and I am currently working on a stacked column chart. I believe that the data I am using is not structured correctly for the chart to work as intended. That is why I am looking for a way to group two columns into one like in this example I made in Excel:

Example

The goal of the chart is to compare the Planned and Committed Hours for each phase.

I tried to look for solutions on how to these changes in Power BI but could not manage to make it work.

Let me know if you need further information. Thanks for the help!

[amitchandak · Accepted solution]

Refer if this can help: https://radacad.com/pivot-and-unpivot-with-power-bi

Else Try to create a table like this and rename columns later

new table =
union(
summarize(table,name,table[commitment_phase1],table[commitment_phase2],"commitment/planned","commitment"),
summarize(table,name,table[planned phase 1],table[planned phase2],"commitment/planned","planned")
)

OR

new table =
union(
selectcolumns(table,name,"Phase 1" ,table[commitment_phase1],"Phase 2",table[commitment_phase2],"commitment/planned","commitment"),
selectcolumns(table,name,"Phase 1",table[planned phase 1],"Phase 2",table[planned phase2],"commitment/planned","planned")
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Pragati11 · Accepted solution]

Hi @PatrickH ,

I tried with your sample data and was able to do it in Edit Query in Power BI as follows:

Select the columns as shown below and click on the drop-down again "Unpivot Columns" and select "Unpivot Only Selected Columns"

You get the following:

Duplicate the "Attribute" column as shown and click on "Extract" option as shown below:

Give a character space in the dialog below and click OK:

 You see the following:

Rename this column to "Phase" as shown below:

Select "Attribute" column as shown below and give space character and hit OK to the dialog box similar to we did above: 

You will get the following:

Now comes the final step where we will use Group BY functionality on "Phase" column. 

You will see following dialog box appearing. Give the settings as shown below:

You will get following output:

 Click on the icon highlighted above and you get following:

After Clicking OK, you get the following:

Remove the 1st "Phase" column as highlighted above and rename rest of the other columns as required. You will get the following:

Last Step Finally. Select "Phase" column and click "Pivot Column" as highlighted in above picture. A dialog appears as follows. Make sure to select "Value" column here because we want it on this column and hit Ok. We get the desired result:

Hope this helps. 🙂

Please mark this as a solution if it works for you!

Thanks,

Pragati

[Pragati11]

Hi @PatrickH ,

Kindly mark this as a solution if it worked 🙂

Thanks,

Pragati

[amitchandak · Accepted solution]

Refer if this can help: https://radacad.com/pivot-and-unpivot-with-power-bi

Else Try to create a table like this and rename columns later

new table =
union(
summarize(table,name,table[commitment_phase1],table[commitment_phase2],"commitment/planned","commitment"),
summarize(table,name,table[planned phase 1],table[planned phase2],"commitment/planned","planned")
)

OR

new table =
union(
selectcolumns(table,name,"Phase 1" ,table[commitment_phase1],"Phase 2",table[commitment_phase2],"commitment/planned","commitment"),
selectcolumns(table,name,"Phase 1",table[planned phase 1],"Phase 2",table[planned phase2],"commitment/planned","planned")
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin