cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

How to transpose data for a stacked chart

Hi, I have the following sample data (this is a subset of the main table):

Contract IdRisk Cat 1Risk Cat 2Risk Cat 3Risk Cat 4
11233
22331
32311
42312
52322
62322

 The numbers in the Risk columns represent  1 = High 2 = Medium 3 = Low

 

I would like to show a stacked chart by Risk Cat and by H/M/L so I would probably need the data flattened as follows:

 

Risk NameHighMediumLow
Risk Cat 1150
Risk Cat 2015
Risk Cat 3222
Risk Cat 4231

and create a visualisation as below:

Screenshot 2020-10-28 at 20.07.45.png

 

 

 

 

 

 

 

 

 

Any ideas on how i can do this? 

 

Thanks in advance!

1 ACCEPTED SOLUTION
Super User IV
Super User IV

To do this, in the query editor, select/highlight your Contract_Id column and right click and choose Unpivot Other Columns.  Rename the Attribute and Value columns if you want with Risk Category and Risk Value (or something like that), then load the table.

 

Then just make a bar chart using Attribute on the axis, Value on the legend, and the count of contract Ids on the values, to get the chart below.

 

mahoneypat_0-1603920952102.png

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
Super User IV
Super User IV

To do this, in the query editor, select/highlight your Contract_Id column and right click and choose Unpivot Other Columns.  Rename the Attribute and Value columns if you want with Risk Category and Risk Value (or something like that), then load the table.

 

Then just make a bar chart using Attribute on the axis, Value on the legend, and the count of contract Ids on the values, to get the chart below.

 

mahoneypat_0-1603920952102.png

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

@mahoneypat   I dont think this is going to work.

 

I have a main table and then i created a sub table but not through query editor.  If i dont have the table in the query editor view then i cannot do what you have suggested.  I created a Risk Table as below :

 

Risk Table = SELECTEDCOLUMNS('Main Table',"<Name of new Column>", <Main Table.Columns Name>......)

 

is there another option?

 

The best place to do all this is in some ETL later - given this is spreadsheet based i would like to do this in Power BI... do i have any other options?

The ETL part really needs to be done in the query editor.  Some stuff is possible with DAX tables, but it is not clunky or sometimes not possible.  You don't have to change your Main table query; however, you can "reference" it in the query editor and make the table you need.  Are you able to do that (open query editor, right click on your Main query and choose "Reference")?

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

 

Ok so i referenced the original table and chose the fields that i needed.

I then unpivoted the columns as you mentioned and was able to create the chart that i needed.

 

Thanks for your help.

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors