Forum Discussion
How to make a clustered column chart with multiple fields
Hello,
I am trying to build a frequency chart to show the total number of each resource type that staff provided (e.g. # of basic needs resources, # of diapers resources, # of childcare resources, etc).
I started with a table of all resources provided. I then created calculated fields for each type of resource, so that each resource type is a binary (1 = true 0 = false).
I then put each of these calculated fields into a clustered column chart. When I set the y axis to 'sum', I get the right values (ie. a count of all the 1's), but the chart doesn't look good. I read something about creating a text index field to put in the x-axis, so I just created a dummy variable where all rows = "Number of Referrals". However, this didn't really help much. There aren't any labels along the x axis and the labels above look clunky. There's also still a lot of white space to the left and right of the columns that I can't get rid of.
Is there a better approach to do this? I see many people posting the same question, but haven't found a workable solution!
In case it's relevant, I'm working in a report that uses the above semantic model as my data source, so I have to go back to the semantic model file to edit the query.
Thanks for any assistance you can offer!
Thanks for the reply from MockIT2As and Fowmy , please allow me to provide another insight:
Hi, data4good
Thanks for reaching out to the Microsoft fabric community forum.First I have created the following table and the column names and data are the data you have given:
Here are the detailed steps for your reference, which I hope will help you proceed more clearly:
1.Firstly, copy the query.In Power Query, right-click the original query and select "Duplicate.
This preserves the original data, avoiding risks associated with direct modifications.
2.Secondly, Unpivot operation:Select all columns except the "Employee" column and choose "Unpivot Columns."
This converts the data from a wide table to a long table format, making it easier to analyze.
3,Next, create a bar chart.In Power BI Desktop, select the bar chart visualization.
Drag the "Attribute" field to the X-axis.
4.Then, modify visualization settings:
Change bar chart colors.Create a new measure to define colors for different categories.
ColorCode = SWITCH ( MAX ( 'Table (2)'[Attribute] ), "Basic Needs", "#FF5733", "Diapers", "#33FF57", "Childcare", "#3357FF", "Food", "#FF33A6", "Clothing", "#FFC300", "#FFFFFF" )Modify visualization title.In the "Format" tab, find "Title" and enter the new title text.
Add column titles.In the "Format" tab, find "Data Labels," enable them, and set the label format.
Remove the title from the x and y axes.
5.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ah thank you so much! Your response made me realize the step I was missing, and it was actually even simpler than what you laid out. I actually already had a table of data in long format, and I had used calculated columns to create a series of boolean columns. Following your logic, I was able to create an additional calculated column that combined all the boolean columns. Thank you!!
4 Replies
Thanks for the reply from MockIT2As and Fowmy , please allow me to provide another insight:
Hi, data4good
Thanks for reaching out to the Microsoft fabric community forum.First I have created the following table and the column names and data are the data you have given:
Here are the detailed steps for your reference, which I hope will help you proceed more clearly:
1.Firstly, copy the query.In Power Query, right-click the original query and select "Duplicate.
This preserves the original data, avoiding risks associated with direct modifications.
2.Secondly, Unpivot operation:Select all columns except the "Employee" column and choose "Unpivot Columns."
This converts the data from a wide table to a long table format, making it easier to analyze.
3,Next, create a bar chart.In Power BI Desktop, select the bar chart visualization.
Drag the "Attribute" field to the X-axis.
4.Then, modify visualization settings:
Change bar chart colors.Create a new measure to define colors for different categories.
ColorCode = SWITCH ( MAX ( 'Table (2)'[Attribute] ), "Basic Needs", "#FF5733", "Diapers", "#33FF57", "Childcare", "#3357FF", "Food", "#FF33A6", "Clothing", "#FFC300", "#FFFFFF" )Modify visualization title.In the "Format" tab, find "Title" and enter the new title text.
Add column titles.In the "Format" tab, find "Data Labels," enable them, and set the label format.
Remove the title from the x and y axes.
5.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ah thank you so much! Your response made me realize the step I was missing, and it was actually even simpler than what you laid out. I actually already had a table of data in long format, and I had used calculated columns to create a series of boolean columns. Following your logic, I was able to create an additional calculated column that combined all the boolean columns. Thank you!!
