Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mal_Sondh
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
mahoneypat
Employee
Employee

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
mahoneypat
Employee
Employee

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


@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.