Forum Discussion
Clustered column chart: show only values with data
Hi,
I have a clusterd column chart that shows various fields from my data. However, I want to show only the fields that have values in it. Ideally, I'd like the legend to only show what's in the graph. See my example:
background:
Each row of data is from a facility providing specific food. Each food item is a seperate column. Values will show null if they facility did not provide that food. See below:
In a perfect world i would just have the visual show only values greater than 0. Or, if there's a way to filter the legends for only values that are in the graph rather than a monster list of 12 when there's only 4 columns returning data. How do I best achieve this?
Thank you!
Anonymous
I used your sample data and created what you asked for.
Here are the steps I did in Query editor.
1. Before automatic type conversion, select the columns other than District,ward,school,month(select green_vegetables till salt - 11 columns ; Make sure the selected columns are in alphabetic datatype)
2. In the ribbon, click Transform->unpivot dropdown->selected columns. After this step, the data will look like below.
3. Change the value column to decimal number format.
4. Close & Apply
Now create a new table as below. (I didn't rename the attribute column in previous step)
Define a relationship between the tables based on Attribute.
Create the visual as below.
If it helps, mark it as a solution
Kudos are nice too
4 Replies
Anonymous
Modify the filter direction to both/bidirectional in the relationship between the table in screenshot and legend attribute table.
Update:
If you do not have a seperate dimention for attribute in legend, create a new table as below and define relationship between the tables. Use the attribute from new table as legend.
If it helps, mark it as a solution
Kudos are nice too
Hi VasTg ,
thank you for the response.
I'm not sure I understand since my legend is not a seperate table, but instead each value in the chart is a seperate column. I'm not sure I can create a new table for my legend and link them.
Here's a sample of what it looks like with the example of the data driving the visual:
This is the underlying data with each column representing each value in the visual above. As you can see there are 10 fields in the legend but only 4 have values when you sum for each respective column. I'd like to have the legend correspond with the graph. Potentially new records could be any of the 10 potential options.
location District location Ward location School_id Month green_vegetables fruits other beans maize sorghum rice OFSP millet banana salt Bunda- Urban Kabasa 1094 January null null null null null null null null null null null Butiama Buhemba 1149 January null null null null null null null null null null null Musoma Bukumi 1159 January 20 null null null null null null null null null null Bunda DC Butimba 1033 January null 20 null null null null null null null null null Butiama Mirwa 1133 January 150.4 null null null null null null null null null null Butiama Buhemba 1148 January null null null null null null null null null null null Butiama Buhemba 1151 January null null null null null null null null null null null Butiama Buhemba 1152 January null null null null null null null null null null null Butiama Buhemba 1150 January null null null 85 null null null null null null null Butiama Mirwa 1134 January null null null null null null null null null null null Butiama Mirwa 1132 January 330 null null null null null null null null null null Butiama Masaba 1143 January 50 null null null null null null 121 null null null Butiama Masaba 1145 January null null null null null null null null null null null Butiama Masaba 1146 January null null null null null null null null null null null Butiama Masaba 1147 January null null null null null null null null null null null Butiama Buruma 1128 January null null null null null null null null null null null Butiama Buruma 1130 January 20 null null null null null null null null null null Butiama Buruma 1129 January null null null null null null null null null null null thank you and hope my explanation makes sense. Thanks for taking the time to help.
Anonymous
I used your sample data and created what you asked for.
Here are the steps I did in Query editor.
1. Before automatic type conversion, select the columns other than District,ward,school,month(select green_vegetables till salt - 11 columns ; Make sure the selected columns are in alphabetic datatype)
2. In the ribbon, click Transform->unpivot dropdown->selected columns. After this step, the data will look like below.
3. Change the value column to decimal number format.
4. Close & Apply
Now create a new table as below. (I didn't rename the attribute column in previous step)
Define a relationship between the tables based on Attribute.
Create the visual as below.
If it helps, mark it as a solution
Kudos are nice too