How to Add Total in Bar Chart Power BI

Adding a total to a bar chart in Power BI seems like it should be a one-click process, but it isn't a built-in feature. This is because bar and column charts are designed for comparing individual category values, and adding a total can sometimes distort the visual scale. That said, it's a very common request, and this article will walk you through a few practical methods to get it done, from a quick visual trick to a more powerful DAX solution.

Choose Your Method: Comparing the Options

There are multiple ways to display a total in or alongside your bar chart. Each comes with its own benefits and drawbacks.

• Overlay a 'Card' visual: The fastest way to add a dynamic total. It's simple but requires some manual alignment. Best for static dashboards where you won't need to resize visuals often.
• Add a 'Total' bar with DAX: The most robust and flexible solution. The total becomes part of your data, interacts perfectly with filters, and sorts correctly. This requires writing a simple DAX query but is the best practice for most reporting needs.
• Use a 'Matrix' with data bars: An alternative to a bar chart that natively supports totals. This mimics the look of a bar chart and may be the simplest option if you don't want to write any DAX.

Method 1: The Quick Visual Trick (Overlaying a Card)

This approach involves creating two separate visuals - your bar chart and a card that shows the total - and then grouping them together so they act as one. It's incredibly fast and requires no code.

Step-by-Step Instructions

1. Create Your Bar Chart: Start by creating a standard Bar or Column Chart. For our example, let's use a sample sales dataset and place Country on the Y-axis and Sales Amount on the X-axis.
2. Create a Card Visual: Click on a blank space on your report canvas and select the Card visual from the Visualizations pane. Drag your numerical field (e.g., Sales Amount) into the 'Fields' well. The card will now display the grand total.
3. Format the Card: By default, the card visual has a large font and a category label. We need to slim it down.
4. Position and Group: Drag the newly formatted card and place it in a logical position over your bar chart, like in the top-right corner. To ensure they stay together, hold the Ctrl key and click on both the bar chart and the card visual. Right-click on one of them and select Group > Group. Now, they will move and resize as a single locked element.

Pros: Very fast and intuitive, no formulas needed.

Cons: The positioning is manual and can feel clumsy. If your chart data is filtered, the card's total will adjust correctly, but the alignment can look off if the bars change size dramatically.

Method 2: Adding a 'Total' Bar Using DAX

This is the definitive method for creating a truly integrated total bar. By writing a simple DAX query, you'll create a new summary table that includes a "Total" row. This new table will then serve as the data source for your chart.

The Concept: Using UNION to Add a Total Row

The plan is to create a new calculated table that does two things:

1. First, it lists out each of our categories (e.g., each country) and their sum of sales.
2. Second, it creates a single, separate row labeled "Total" that contains the sum of all sales.

Finally, we'll combine these two results into one table using the UNION function.

Step-by-Step Instructions

1. Create a New Calculated Table: Navigate to the Modeling tab in Power BI Desktop and select New table. This will open the formula bar for you to enter your DAX expression.
2. Enter the DAX Formula: Copy and paste the following formula into the formula bar. Be sure to replace 'Sales'[Country] and 'Sales'[Sales Amount] with your own table and column names.

Report Table with Total = 
UNION(
    SUMMARIZE(
        'Sales',
        'Sales'[Country],
        "Display Name", MAX('Sales'[Country]),
        "Sales", SUM('Sales'[Sales Amount]),
        "SortOrder", 0
    ),
    ROW(
        "Display Name", "Total",
        "Country", "zzzz",  // Value to help with sorting
        "Sales", SUM('Sales'[Sales Amount]),
        "SortOrder", 1
    )
)

Deconstructing the Formula:

• SUMMARIZE(...): This function groups your data by the specified column ('Sales'[Country]) and calculates the sum of sales for each country. We create a SortOrder of 0 for all these individual countries.
• ROW(...): This creates a single, manual row for our total. We give it a Display Name of "Total" and a SortOrder of 1. The 'Country' column has a value of "zzzz" - this is a trick to help with sorting later.
• UNION(...): This stitches the two table results together into one.

1. Build the Chart with the New Table: Now that you have your new calculated table named Report Table with Total, create a new bar chart.

You will see a chart with a "Total" bar.

1. Sort the Total Bar to the Bottom: By default, the chart sorts alphabetically, so "Total" appears in the middle. To fix this:

Because we assigned 0 to all countries and 1 to our total, the total bar will now always appear at the bottom!

Pros: A rock-solid, fully interactive solution. The total bar filters, updates, and sorts perfectly within the chart itself.

Cons: Requires you to be comfortable writing and editing a simple DAX formula.

Method 3: Alternative - Using a Matrix with Data Bars

If the methods above feel too complicated, you might rethink whether you actually need a Bar Chart visual. Power BI's Matrix visual is designed to handle totals natively and can be formatted to look almost identical to a bar chart.

Step-by-Step Instructions

1. Insert a Matrix Visual: Select the Matrix visual from the Visualizations pane.
2. Configure Fields:

You will see a table with a grand total at the bottom.

1. Add Data Bars: To turn the numbers into visual bars:
2. Final Polishing: To make it look more like a chart and less like a table, go to the Format your visual pane and adjust settings like Style presets (try "None"), Gridlines, and font sizes until it achieves the clean look you want.

Pros: Extremely easy and leverages Power BI’s built-in subtotal functionality. It's the "batteries included" approach.

Cons: While it functions perfectly, it is still technically a matrix visual and may have formatting or interaction limitations compared to a true bar chart visual.

Final Thoughts

While Power BI doesn't offer a single-click "show total" button on bar charts, these workarounds provide flexible and powerful solutions. For a quick dashboard mockup, overlaying a card is sufficient, but learning the DAX UNION method will give you clean, professional, and robust reports every time.

These manual workarounds are exactly why we created products like Graphed. Building powerful reports shouldn't require complex formulas or learning the nuances of visual formatting. We let you simply connect your data sources, like Google Analytics or your CRM, and use natural language to create reports. Instead of spending 20 minutes on DAX, you could just ask, "Show me a bar chart of sales by country, and add a total at the bottom," and we would build the interactive dashboard for you in seconds.