Forum Discussion
Total Column in a Clustered Bar chart
Hi,
To get a Total Bar in a clustered column chart.
Need a total bar along with the four bars as shown in the below image.
Thanks in advance.
Hi Anonymous
You can refer to the following example
Sample data
1.Create a new Type table
Type = var a=SUMMARIZE('Table','Table'[Year_Month]) var b={"Total"} return UNION(a,b)2.Then create a measure to calculate the sum
Sum = VAR a = SUMMARIZE ( FILTER ( 'Type', [Year_Month] <> "Total" ), [Year_Month] ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Year_Month] IN a )3.Then create a measure
Sum_display = VAR a = SUMMARIZE ( ALLSELECTED ( 'Type' ), [Year_Month], "Sum", [Sum] ) RETURN IF ( SELECTEDVALUE ( 'Type'[Year_Month] ) = "Total", SUMX ( a, [Sum] ), [Sum] )4.Put the column of type table and the sum_display measure to the visual
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous
You can refer to the following example
Sample data
1.Create a new Type table
Type = var a=SUMMARIZE('Table','Table'[Year_Month]) var b={"Total"} return UNION(a,b)2.Then create a measure to calculate the sum
Sum = VAR a = SUMMARIZE ( FILTER ( 'Type', [Year_Month] <> "Total" ), [Year_Month] ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Year_Month] IN a )3.Then create a measure
Sum_display = VAR a = SUMMARIZE ( ALLSELECTED ( 'Type' ), [Year_Month], "Sum", [Sum] ) RETURN IF ( SELECTEDVALUE ( 'Type'[Year_Month] ) = "Total", SUMX ( a, [Sum] ), [Sum] )4.Put the column of type table and the sum_display measure to the visual
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- 123abc
Community Champion
Thank you for providing the example and steps you've taken to create a clustered column chart with a total bar in Power BI. It looks like you've already created the necessary measures and set up your visual correctly. Here's a summary of the steps you've taken:
- Created a new table called "Type" with a "Total" category.
- Created a measure called "Sum" to calculate the sum of values excluding the "Total" category.
- Created a measure called "Sum_display" to display the sum values and handle the "Total" category separately.
- Placed the "Type" table and the "Sum_display" measure in the visual.
Your output appears to be a clustered column chart with the "Total" bar included, which is exactly what you were looking for. This approach is common in Power BI for displaying a total bar in a clustered column chart.
If you have any specific questions or need further assistance with this Power BI setup, please feel free to ask, and I'll be happy to help.
JLambs20Helper III
Any idea how to get this to sort correctly when working with a full year since you'll have 2023-1 and 2023-10 and BI will format the "Year_Month" column in the "Type" table as text? Also, if it could be scalable to include future years, that would wonderful. Great solution otherwise though!
- 123abc
To add a total bar to a clustered column chart in Power BI, you can follow these steps:
Create Your Clustered Column Chart:
- Start by creating a clustered column chart with your data.
- Place the relevant fields in the "Axis" and "Values" sections of the visualizations pane to create your clustered bars.
Calculate the Total:
- You'll need to create a measure to calculate the total value that you want to display as a separate bar in the chart.
For example, if you want to calculate the total of a column called "Value," you can create a DAX measure like this:
Total Value = SUM(YourTableName[Value])
Replace YourTableName with the actual name of your table and Value with the name of the column you want to total.
Add the Total to the Chart:
- Now, drag and drop the "Total Value" measure you created in step 2 into the "Values" section of your clustered column chart in the visualizations pane.
Format the Total Bar:
- By default, the total bar may appear as another clustered column within your existing clusters. To make it a separate total bar, you'll need to format it.
- In the "Visualizations" pane, click on the chart to select it.
- In the "Format" section, expand "Data labels" and turn off the "Data label" option. This will remove labels from the total bar, making it look cleaner.
- You can also format the total bar's color and style to distinguish it from the other bars if needed.
Adjust Axis and Scales:
- Depending on your data, you may need to adjust the axis and scales to ensure the total bar doesn't skew the visualization. You can do this in the "Format" section under "Axis" and "Y-Axis."
Now, your clustered column chart should include a separate total bar alongside the clustered bars, displaying the total value you calculated. You can further customize the appearance of the total bar to match your desired chart aesthetics.
Adamp1916Helper I
This doesn't work. It doesn't create a separate total column. It just adds a bunch of totals columns next to the other ones with the exact same values.
ashuaswinireddyAnonymous Can you please let me know how to get total column for running total. I tried this method for running total and its not working
"Show datapoint as a table" feature is broken while doing this. no use with this workaound as another drill down page has to be build to show the underlying data.
