Add total on top of stacked bar chart (PowerBI Report Builder)

[CharlotteB]

Hello,

Is there a way to add a total on top of each stacked bar in PowerBi Report Builder? I have a series group so I see the total for each series group but I want the grand total on top of each bar (red box in image below).

Thank you in advance!

[v-pnaroju-msft]

Hi @CharlotteB,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Hi @CharlotteB,

Thank you for your update.

The reason you observe the total appearing multiple times is due to the way the chart evaluates the total expression for each series group (Category). This behavior is expected in Power BI Report Builder when using a stacked bar chart with multiple series.

To display the total only once per bar, you may apply a simple filter on your total line series to restrict it to a single category (for example, "Office"). This will ensure that the total is calculated correctly and displayed only once, thereby keeping your chart clean and accurate.

Please follow the steps below:

1. Navigate to the chart data and select "Series" under Values. Right-click and select "Series Properties."
2. In the window that opens, click on the "fx" button beside "Value field" and add a filter to limit the total line series to one category, as demonstrated in the screenshot attached below.
   
   
   

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Should you have any further questions, please feel free to contact the Microsoft Fabric community.

Thank you.

[CharlotteB]

THANK YOU!! you have been so helpful I really appreciate it! 

[v-pnaroju-msft]

Hi CharlotteB,

Thank you for your follow-up.

The error you are encountering arises because the scope parameter ("Chart1_CategoryGroup") is not recognized as a valid grouping or dataset name within the context of the chart. This issue is commonly faced when attempting to display a total value label on top of a stacked bar chart in Power BI Report Builder (Paginated Reports), especially when series groups are involved.

Kindly follow the steps below to resolve the issue:

1. Open Power BI Report Builder and under the chart data, select "Region" within the Category Groups. Right-click and choose "Category Group Properties". A new window will open, where you need to note down the "Name" field. This value will replace the scope parameter "Chart1_CategoryGroup" in your expression.

2. Next, navigate to chart data and select "Series" under Values. Right-click and choose "Series Properties". A new window will open. Click on the "fx" button beside the "Value field" and update the scope parameter with the correct value obtained in Step 1.

Please find the attached screenshots for your reference:

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who may be facing similar issues.

Should you have any further queries, please feel free to reach out to the Microsoft Fabric community.

Thank you.

[CharlotteB]

Thank you so much for this response! I am so close to what I want - do you know if there is a way to only have the totals display once? They are displaying three times because of my series 

[v-pnaroju-msft · Accepted solution]

Thankyou, @grazitti_sapna, for your response.

Hi @CharlotteB,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Please find attached the screenshot as below, which may assist you in resolving the issue:

Kindly follow the steps below to resolve the problem:

1. Load the data into Report Builder and add a Stacked Bar chart onto the report canvas.

2. Configure the chart data as follows:
   a. Drag Region into the Category Groups (X-Axis) pane.
   b. Drag Category into the Series Groups pane.
   c. Drag Sales into the Values pane. It should default to Sum(Sales). You will now see stacked bars displayed per Region, coloured by Category.

3. Add a New Series to represent the Total Values.
   3.1. Add a Total Series:
   a. In the Chart Data pane, click the green plus sign under Values again.
   b. Add a new value by entering the expression (fx):
   =Sum(Fields!Sales.Value, "Chart1_CategoryGroup")
   This expression calculates the total sales per Region (grouped by Category Group). Please update "Chart1_CategoryGroup" to match your actual group name, if it differs.
   3.2. Change the New Series to a Line:
   a. Select your chart by clicking on it.
   b. Click once on one of the newly added bars (which should be of the same height as the full bar).
   c. Right-click and select Change Chart Type.
   d. In the Chart Type window, change this second series to Line, then click OK. The line will now overlay the bars, tracking the total height.
   3.3. Show Data Labels for the Line (Total Values):
   a. Right-click directly on the newly created line.
   b. Choose Show Data Labels. The chart preview will now display numeric totals above each stacked bar.

4. Format the Total Labels Neatly:
   4.1. Format the Line Style (Make the Line Invisible):
   a. Right-click on the line series.
   b. Choose Series Properties.
   c. Under the Appearance section, select Border Style and choose None. This will make the line invisible, leaving only the data labels (totals) visible above each bar.
   4.2. Style the Total Labels:
   a. Click once on one of the total value data labels.
   b. Right-click and select Series Label Properties.
   c. Apply the following settings: set the font to Bold, increase the font size slightly (e.g., 10 or 12 pt), and choose a dark or contrasting colour (e.g., Black). This will ensure that the totals are clearly visible and easy to read above each stacked bar.

5. Preview Your Report:
   a. Click Run on the top toolbar to preview the chart.
   b. You should now see stacked bars arranged by Region, with coloured segments for each Category and a numerical total displayed above each bar.

6. Save the report file by selecting File > Save As, and save your .rdl file.

If you find our response helpful, kindly mark it as the accepted solution and give kudos. This will assist other community members who are facing similar queries.

Should you have any further questions, please feel free to reach out to the Microsoft Fabric Community.

Thank you.

[CharlotteB]

Thank you for your response! When I try to do step 3b, =Sum(Fields!Sales.Value, "Chart1_CategoryGroup" , I get the below error. (I am replacing the "Chart1_CategoryGroup" with my category name). Any ideas?

[grazitti_sapna]

Hi @CharlotteB 

Please enable both the options(Data Lables, Total Labels) in Format Pane then you will be able to see total for each series group on top of each bar.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[CharlotteB]

Do you know how to do this in PowerBi Report Builder? That's where I'm unclear