Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I have the following sample data (this is a subset of the main table):
Contract Id | Risk Cat 1 | Risk Cat 2 | Risk Cat 3 | Risk Cat 4 |
1 | 1 | 2 | 3 | 3 |
2 | 2 | 3 | 3 | 1 |
3 | 2 | 3 | 1 | 1 |
4 | 2 | 3 | 1 | 2 |
5 | 2 | 3 | 2 | 2 |
6 | 2 | 3 | 2 | 2 |
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 Name | High | Medium | Low |
Risk Cat 1 | 1 | 5 | 0 |
Risk Cat 2 | 0 | 1 | 5 |
Risk Cat 3 | 2 | 2 | 2 |
Risk Cat 4 | 2 | 3 | 1 |
and create a visualisation as below:
Any ideas on how i can do this?
Thanks in advance!
Solved! Go to Solution.
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.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |