[How to] Add total values in bar and line charts in Power BI

A while back I did some work for a client which involved adding total values to a bar chart in Power BI. This isn’t an out of box feature in Power BI at the moment. Therefore I thought it would be of some value to write about it here and show you one way of doing this in Power BI.

In this post I will guide you through how to add a Total bar value in your line or bar chart and also how to easily switch between including/excluding the Total.

Research

When I first got the request I did some research on how to solve it. It didn’t take long before I discovered that there are some suggestions around. For example there’s a Power BI community post here where Community support team member v-chuncz-msft has written some nice posts, which i’ve used as inspiration for this solution. And mainly the idea of this post is to have a beginning-to-end guide on how to implement this in your report.

Things to consider

Showing total values in a bar or line chart could present some misunderstanding and readability issues. This is especially something to consider from an end-user perspective. Therefore it’s wise to always be careful before adding this kind of features into your chart.
It can for example be confusing for end users if you mix values in your chart together with aggregated values.
End-users could also have a harder time reading or comparing the data. Especially when adding aggregated values on the same axis. This would result in all other values would be scaled down visually in size.

However, there are of course times where this could be very useful. There are times when you want to compare the values between each specific category and the total in the same chart. Then this could be of great benefit instead of having a total value represented outside of the chart in another context.

How to

To show you how this can be done in Power BI, I am using the Contoso dataset. Which, at the time of writing this post, is available here.

To get you an idea of how the result will be, I will be creating a chart which shows the sales amount by each sales channel, as the image shows below.

Step 1 – Preparing some data

The data used for this comes from fact table “FactSales” and dimension table called “DimChannel”

Relationship view

The axis values of a bar chart needs to come from fixed values in a column. Because we want to have “Total” as a value in our axis, we first need to create a calculated table.

We are creating the table using DAX in this example, but can of course also be done using Power Query.

The first step is to identify all values that we want to display in the chart. On top of that we then add a line which holds the text for total.

This is the DAX used for creating the new table.

DimChannelTotal =
UNION ( VALUES ( DimChannel[ChannelName] ); ROW ( "Total"; "Total" ) )

To describe the this, we first take all channel names from table DimChannel using the VALUES() function. Then we add a new line at the bottom with the value “Total” using ROW() and UNION(). The result looks like this:

DAX Calculated table

For simplicity this new table will be disconnected and not have any relationships with other tables. You could want to be able to filter these values based on selections made in the real dimension table. If so you could add an ID/Key column and create a 1:1 relationship between them.

Relationship view including the new table

Step 2 – Preparing some data

Now lets create a second table which will simply hold two values – “Yes” and “No”. These values will enable the end users to select if they want to include or exclude the total value in the chart.

Enter data in Power BI

This table can be created in many different ways, but I’ve added it using the method “Enter Data” in Power BI.

Your data model should now look something like this below.

Step 3 – Creating the measure

Now it’s time to do the magic and take use of the tables that we’ve just created. To do that we need to create a measure. In this demo we will be using one measure which returns the sales amount from table FactSales.

SalesAmountWithTotal = 
VAR _CurrentAxis = SELECTEDVALUE('DimChannelTotal'[ChannelName])
RETURN


IF(_CurrentAxis = "Total";
    IF(SELECTEDVALUE(YesNo[Values];1) = "Yes";
        SUM(FactSales[SalesAmount]);
        BLANK()
    );
    
    CALCULATE(
        SUM(FactSales[SalesAmount]);
        DimChannel[ChannelName] = _CurrentAxis
    )
)

Lets break this down.
The first part is a variable called _CurrentAxis. This variable holds the value of the current axis context. That means that every time the measure is evaluated (for each axis) it will return the current axis name and store that in this variable.

The second part is a nested IF statement. First we do a check to see if the measure is evaluating on the “Total” axis. If this is the case we will do another IF statement.

IF(SELECTEDVALUE(YesNo[Values];1) = "Yes";
        SUM(FactSales[SalesAmount]);
        BLANK()
    )

The second IF statement checks the value from our table we created in Step 2 and checks if the value “Yes” is selected. If that returns true it will return the total sum of all SalesAmount from FactSales. If it returns false, e.g. “Yes” is not selected, then it will return BLANK() which means that the total value will not show up in the chart.

The third part in the measure is the “false” part of the outer IF statement. At this time we know that the current context is not the “Total” axis but something else. Since all other values in our axis are real channel names we know that the variable holds a name for a sales channel.

CALCULATE(
        SUM(FactSales[SalesAmount]);
        DimChannel[ChannelName] = _CurrentAxis
    )

By using the CALCULATE function we return the total sum of SalesAmount from FactSales but with a filter on ChannelName. This means that for each evaluation the measure will only return the sales amount for the sales channel in the current context.

Step 4 – Creating a bar chart

It’s now time to create a bar chart and start looking at the result from our preparations. Simply create a bar chart from the visualization pane and put column “ChannelName” as “Axis” and your newly created measure in the “Value” section.
The “ChannelName” column should be the one from the table we created in step 1.

You should end up with something that looks like this:

Newly created bar chart

As you probably notice we are missing the total value in the chart. This is because the measure “SalesAmountWithTotal” that we just wrote is looking at the selection of the Yes/No values that we created. Just create a new slicer with the values from the Yes & No table column and select “Yes”

Bar chart with total value

Step 5 – Line charts

You can also use this technique in for example a line chart. Instead of using the column values from ChannelName as the axis, use them as a legend. The sales amount measure goes in to the values and as axis you can use a date dimension. When setting this up using the contoso dataset we get this:

Step 6 – Done

After some formatting – changing some colors, adding labels, slicers and changing the fonts we can get something like this.

Wrap up

I’ve now showed you one way of implementing total values in your bar or line charts. This is perhaps not a solution that fit all scenarios. However it’s one of those things you can put in your toolbox for whenever you may need it in the future.

I hope that you have enjoyed this quick guide and that you get to use this in your future Power BI reporting. If you like the post or have any questions, please feel free to post a comment below.

If you wan’t to get in touch, you can do that here.

Please follow and like:
error

Leave a Reply

Your email address will not be published. Required fields are marked *