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

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

Accepted Solutions
Super User III
Super User III

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? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User III
Super User III

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? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




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? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




@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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors