Forum Discussion
Stacked column chart with multiple columns groupe into categories
Hi, I am having some kind of difficulties dealing with grouping columns under custom categories. I have the following dataset as an example:
I have 9 temperature sensors that can give 3 values: Low, Medium, High.
The dataset also has around 12 other columns for other values.
| id | sensor1 | sensor2 | sensor3 | sensor4 | sensor5 |
| 1 | Low | Low | Medium | High | High |
| 2 | Medium | Medium | Low | Medium | High |
| 3 | Medium | Medium | Medium | High | High |
I am trying to parse my data into powerbi with a stacked column chart. However, I want to regroup the sensors under 2 categories. Category A will include sensors 1 2 and 3 and Category B will include sensors 4 and 5.
The chart I would like to have as a result is something like that.
I am counting the instances when there is a "low medium high" listed for each sensor.
I have tried to unpivot method into another table but that second table ended up having as many rows as my reference one. I have read a little bit about disconnected tables but did not really figure out how and if that can solve my problem with a table that would look like that as a reference table for grouping:
| category | subcategory |
| CategoryA | sensor1 |
| CategoryA | sensor2 |
| CategoryA | sensor3 |
| CategoryB | sensor4 |
| CategoryB | sensor5 |
Thank you
3 Replies
- amitchandak
Super User
FredNo , you have to unpivot the same table. Not sure why you want to have another table ?
Unpivoting the main table is not really an option for me because I can only take one row for my calculations. And by unpivoting i am duplicating the rows for the same entry id. I cannot take multiple row for the same entry since I must take the most recent one. I forgot to add in my example a column associated to the employee who is adding the row. I only use the latest entry per employee labeled as the "latest entry" and not the previous ones
So what I ended up doing as a "spaghetti solution" is that I created a table referencing my main table and then delete all rows except the necessary ones and my id column. This gave something like that:
id Attribute Value Category 1 Sensor1 Low GroupA 1 Sensor2 Low GroupA 1 Sensor3 Medium GroupA 1 Sensor4 High GroupB 1 Sensor5 High GroupB 2 Sensor1 Medium GroupA 2 Sensor2 Medium GroupA 2 Sensor3 Low GroupA 2 Sensor4 Medium GroupB 2 Sensor5 High GroupB In my opinion this is not the ideal solution since the new table has as much rows as the reference one but this works for my needs.
