Forum Discussion
100 % Stacked bar chart
Hi Anonymous ,
Do you have any specific column that you use as ID? If yes I would split your model in two table one with all the Portfolio information that you will not unpivot and delete all the columns A to I and a second table that would be the ID column and the unpivot of the A to I columns then linked both tables by the ID column.
To calculate the status you would use the 2nd table and for the rest of the setup the other table.
Hi Anonymous ,
On the query Editor select columns A to I and unpivoted then you will get two columns with attribute (A to J) and Value (status)
Now create a 100% stacked bar with the following layout:
- Axis - Attribute column
- Legend - Value Column
- Value - Count of attribute.
Should give expected result.
Regards,
MFelix
Thanks a lot MFelix for this elegant solution.
I have follow up question to this solution. My database has couple of other columns, and when I unpivot these selected columns within the table a lot of extra rows are created. Also I wanted to filter based on the column in the sheet? How can I do that?
Hi Anonymous ,
Making the unpivot of columns what you do is to create lines instead of columns, so this will increase the number of rows and the data from the other columns that are not unpivot will be duplicated as many time as columns you have. Be aware that depending on the type of information you have associated with your data you may need to have measures or calculation done in a different way so that the information is not multiplied by the many row you have.
When you are refering to filter by a column what do you mean, I cannot see any filter column on the image.
Hi MFelix ,
Sorry for not providing the entire data set.
For example I have a Portfolio column here, i want to use this as a filter.Also, each record represents an application. Unpivoting increases the number of these records and I get a wrong count.
Is there any other way of getting this done?
Kind Regards,
AdarshHi Anonymous ,
Do you have any specific column that you use as ID? If yes I would split your model in two table one with all the Portfolio information that you will not unpivot and delete all the columns A to I and a second table that would be the ID column and the unpivot of the A to I columns then linked both tables by the ID column.
To calculate the status you would use the 2nd table and for the rest of the setup the other table.