Display Total Inside Power BI Donut Chart

Donut charts – second only to Pie charts as the tastiest of all visualizations – are used to illustrate proportions of individual components to the overall whole. They are very similar to Pie charts except the center is hollow although that space usually contains additional information. It has been my experience that Donut charts tend to be favored over Pie charts because they provide increased data density (more information in a single visualization) and they look more modern as compared with old Pie Charts.

While Power BI does contain a Donut visualization, it does not have any out-of-the-box options to fill the center. However, we can create a filled Donut chart using a combination of Data Analysis Expressions (DAX) measures and Card visualizations.

In this example, the Donut chart illustrates spend in various categories. In a default Donut chart, the magnitude and proportion of the individual components are apparent, but the overall total is unknown.

Donut Chart Example
Donut Chart Example

The Data

The data used in this example is found in the screenshot below. It’s a simple Excel sheet with various categories in the rows and months along the columns. For the Donut chart, we want to display the “Category” totals associated with actual spend only, e.g., “Actual – Payroll”, “Actual – Facilities”, “Actual – Telecom”, and “Actual – External”. In other words, we only want the sum of “Total” (Column N) associated with rows 4 through 7.

The values are formatted using a custom number format (referenced below) to display in millions. For instance, if the underlying cell value is 1,000,000, then Excel displays the value as 1.0 using this format.

#,##0.0,,;[Red](#,##0.0,,)
Excel – Donut Sample Data
Excel – Donut Sample Data

Step 1 – Import Data

In the example Excel file, the data is located on a worksheet named “DATA”. Starting with a new Power BI report, click Import data from Excel and select the appropriate file. Perform any initial cleanup transformations as needed. The import results in a Power BI table named “Table 1 (DATA)” which I have renamed to the much more informative name “DATA”.

Power BI Desktop – Imported Sample Data
Power BI Desktop – Imported Sample Data

Step 2 – Add Helper Column

As I mentioned earlier, we only want the Donut chart to display entries associated with actual spend so we add a column to help constrain and filter the data in later steps. In the example data, the rows containing actual spend are conveniently prefixed with the string “ACTUAL – ” which makes the logic straightforward. In this snippet, the new column contains the text “ACTUAL” if the “Category” column begins with the text “ACTUAL – ” in the leftmost substring. All other rows are tagged as “OTHER”. For readability, I use the LEN() function instead of counting the characters in the prefix.

COLUMN_ACTUAL_CATEGORY = IF(LEFT('DATA'[Category],LEN("ACTUAL - ")) = "ACTUAL - ", "ACTUAL", "OTHER")

Our data table now appears (below) with the helper column added to the structure. The appropriate rows are tagged with the text “ACTUAL” in the new column.

Power BI Desktop – Adjusted Sample Data
Power BI Desktop – Adjusted Sample Data

Step 3 – Create DAX Measures

Now that we have our data structured and tagged, we need to create a few DAX measures to populate the center of the Donut chart.

The first measure retrieves the total of all the actual spend entries. Since the sample data already includes a total row, this is trivial since we can simply look for the entry where “Category” is equal to “Total”. Since the underlying table may be influenced by other filters, we use the ALL function in the FILTER for the CALCULATE function in order to ignore any other applied filters and return all rows in the table.

MEASURE_TOTAL_ACTUAL_SPEND_USING_CALCULATE = CALCULATE(SUM('DATA'[Total]),FILTER(ALL('DATA'),'DATA'[Category] = "TOTAL"))

The second measure calculates the sum of the “Total” column in the DATA table. In this measure, the ALL function is not used since we only want to include entries where the helper column is tagged as “ACTUAL” and we want the calculated to be influenced by user selections and filters in the Donut chart visualization, e.g., if the user selects a category in the visualization, then the center calculation should adjust.

MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM = CALCULATE(SUM('DATA'[Total]),FILTER('DATA','DATA'[COLUMN_ACTUAL_CATEGORY] = "ACTUAL"))

The third and final measure calculates the percentage of the selected entries against the whole. By default, the percentage is 100% since all entries are initially selected.

MEASURE_PERCENT_OF_TOTAL = DIVIDE([MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM],[MEASURE_TOTAL_ACTUAL_SPEND_USING_CALCULATE],0)

Click on the newly created MEASURE_PERCENT_OF_TOTAL in the list of Fields. Click on the Measure tools ribbon and change the Format to Percentage. Also change the number of decimal places shown for this value to 1.

Power BI Desktop – Change Measure Format to Percentage
Power BI Desktop – Change Measure Format to Percentage

Step 3 – Create Donut Chart

Switch to the Report view and add a Donut chart visualization. Using the sample data, the Details use the “Category” field and the Values use the “Total” field. The Donut chart displays all of the entries in the data table so we’ll need to use the helper column added earlier. In Filters, use the Filters on this page to include the “COLUMN_ACTUAL_CATEGORY” helper column and select only entries tagged as “ACTUAL”.

Power BI Desktop – Donut Chart Configuration
Power BI Desktop – Donut Chart Configuration

In the Format settings, turn off Background and in the Detail labels section change the Value decimal places to 1.

It is important to turn off the background color so that the center of the Donut chart is transparent. This allows the additional information to be visible in the center.

Step 4 – Create Card Visualizations

We will need two Card visualizations to fill the center of the Donut chart.

Insert a new Card visualization. The first Card represents the second DAX measure created in Step 3 called “MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM”. This is the total of all selected and filtered elements of the Donut chart. Include “MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM” in the Fields for this Card.

In the Format settings, turn off Category and in the Data label section change the Value decimal places to 1 and turn on Bold.

Power BI Desktop – Card Visualization Total
Power BI Desktop – Card Visualization Total

Insert a new Card visualization. The second Card represents the third DAX measure created in Step 3 called “MEASURE_PERCENT_OF_TOTAL”. This is the percentage of the selected and filtered entries in the Donut chart against the total of all actual spend entries. Include “MEASURE_PERCENT_OF_TOTAL” in the Fields for this Card.

In the Format settings, turn off Category and in the Data label section change the Value decimal places to 1.

Power BI Desktop – Card Visualization Percentage
Power BI Desktop – Card Visualization Percentage

Step 5 – Formatting and Clean-up

Now that all of the individual pieces have been created, it is time to put them together into the final product. Align the two Cards in the center of the Donut chart. Adjust the sizing of the Donut chart as well as the Cards and their associated fonts until everything looks as expected. Bring the Donut chart to the front or send the two Cards to the back so that the corners of the Cards do not visually interfere with the Donut chart.

Result

The final chart shows the calculations in the center of the Donut chart as expected.

Power BI Desktop – Donut Chart With Total Inside
Power BI Desktop – Donut Chart With Total Inside

If the user selects or filters on one or more segments of the Donut chart, then the center Cards and underlying DAX measures are recalculated to reflect those selections (screenshot below).

Power BI Desktop – Donut Chart With Filtered Total Inside
Power BI Desktop – Donut Chart With Filtered Total Inside

Combination Clustered and Stacked Column Chart in Excel

While Excel does not have a built-in clustered and stacked chart, one can be created by using a combo chart and a secondary axis.

For context, clustered charts, also known as grouped charts, compare multiple data series grouped by a common category. As an example, a clustered chart may show multiple columns (vertical) or bars (horizontal) next to each other comparing forecast vs. actual spend (series) in any given month (category).

Alternatively, stacked charts compare individual segments or component parts within an individual total column or bar, e.g., actual spend within a given month segmented by spend type.

In this example, the combination clustered and stacked column chart compares forecast vs. actual spend within a given month (clustered column); actual spend is further segmented by spend type (stacked column).

To help visualize, let’s start with a screenshot of the expected result.

Excel – Clustered Column Chart With Stacked Column
Excel – Clustered Column Chart With Stacked Column

Step 1 – Organize The Data

There are actually several approaches to create a combined clustered and stacked chart in Excel. The approach demonstrated in this example keeps the underlying source data structured in a readable format. The alternate approach involves structuring the data with many extra blank cells sprinkled throughout the data. In my opinion, that approach is more difficult and it renders the underlying data unreadable and largely unusable for other purposes.

Our data is structured with the “Forecast” and “Total” actual spend as rows (series) and the months as columns (categories). The “Total” actual spend is further detailed into separate rows (series) for each spend type, e.g., “Actual – Payroll”, “Actual – Facilities”, “Actual – Telecom”, and “Actual – External”. The “Total” row is simply an aggregate of those individual spend type actual values within a given month.

The values are formatted using a custom number format (referenced below) to display in millions. The underlying cell value is 1,000,000 but Excel displays the value as 1.0.

#,##0.0,,;[Red](#,##0.0,,)
Excel – Data Table
Excel – Data Table

Step 2 – Add Helper Data

Next we will add two empty rows (series) below our data. Label these “Spacer 1” and “Spacer 2” and change the font color to white to hide them (assuming the sheet background color is white). These spacer rows are used in a later step to assist with controlling the gap and overlap between columns.

We also add two formulas to calculate the minimum and maximum values of the data series. These values are used in a later step to set the bounds for the Primary and Secondary Axes.

=MIN($B$2:$M$3)
=MAX($B$2:$M$3)
Excel – Adjusted Data Table
Excel – Adjusted Data Table

Step 3 – Create a Clustered Column Chart

Select all of the source data including the spacer rows but excluding the minimum and maximum helper data. On the Insert ribbon, in the Charts section, click the Clustered Column button to insert a new chart.

Excel – Source Data Selection
Excel – Source Data Selection

The resulting chart is displayed with a separate column for each data series in our table. These columns are grouped by month along the horizontal axis.

Excel – Clustered Column Chart
Excel – Clustered Column Chart

Step 4 – Change Chart Types

Click on the chart. In the Chart Design ribbon, click the Change Chart Type. The Change Chart Type dialog box opens. Click the All Charts tab and select Combo.

Each of the data series is displayed with an option to change its Chart Type and Axis. The “Forecast”, “Spacer 1”, and “Spacer 2” data series should have a Chart Type of Clustered Column and Secondary Axis should remain unchecked. The “Actual – Payroll”, “Actual – Facilities”, “Actual – Telecom”, and “Actual – External” data series should have a Chart Type of Stacked Column and Secondary Axis should be checked. The “Total” data series should be changed to Line and Secondary Axis should be checked.

Excel – Change Chart Type
Excel – Change Chart Type
Excel – Combo Chart
Excel – Combo Chart

Step 5 – Adjust the Series Overlap and Gap Width

In the chart, click the “Forecast” data series column. In the Format ribbon, click Format Selection. In the Series Options, adjust the Series Overlap and Gap Width sliders so that the “Forecast” data series does not overlap with the stacked column. In this example, I set both sliders to 0% which resulted in no overlap and a slight gap in between. Depending on your particular data set, these values may require fine tuning.

Excel – Format Data Series Gap Width and Series Overlap
Excel – Format Data Series Gap Width and Series Overlap
Excel – Combo Chart With Adjusted Gap Width and Series Overlap
Excel – Combo Chart With Adjusted Gap Width and Series Overlap

Step 6 – Add Data Labels

For each data series in the chart, click on its column, stacked segment, or line and click Add Data Labels. For the “Total” line, click Format Data Labels and change the Label Position to Above.

Excel – Adjusted Combo Chart With Data Labels
Excel – Adjusted Combo Chart With Data Labels

Step 7 – Adjust Axis Bounds

Depending on the data series, the Primary and Secondary Axes may be misaligned due to differing minimum and maximum bounds. In our example, the upper bound on the Primary Axis is 1.2 while the upper bound on the Secondary Axis is 1.4. We’ll need to align the scales so that the chart columns are visually comparable.

Click on the chart. In the Chart Design ribbon, click Select Data. In the Legend Entries (Series) section, click the Add button. In Series name, click the cell containing the “Primary Scale” header. In the Series values, select the cells containing the corresponding minimum and maximum values.

Excel – Add Primary Scale Series
Excel – Add Primary Scale Series

Repeat the same process for the “Secondary Scale” series.

Excel – Add Secondary Scale Series
Excel – Add Secondary Scale Series

Click on the chart. In the Chart Design ribbon, click the Change Chart Type. The Change Chart Type dialog box opens.

We now see the two new data series in the list. Set the “Primary Scale” series to a Chart Type of Line and Secondary Axis should remain unchecked. Set the “Secondary Scale” series to a Chart Type of Line and Secondary Axis should be checked.

Excel – Change Chart Type Scale Axis
Excel – Change Chart Type Scale Axis

Both the minimum and maximum bounds for the Primary and Secondary Axes are now aligned at 1.4.

Excel – Adjusted Combo Chart With Aligned Scales
Excel – Adjusted Combo Chart With Aligned Scales

Step 8 – Formatting and Clean-up

At this point, there are several extra chart elements that need to be removed or hidden.

In the Legend, click the label for “Spacer 1” so that it alone is highlighted and press delete to remove it. Repeat the same process for “Spacer 2”, “Total”, “Primary Scale”, and “Secondary Scale” legend entries.

Since the Primary and Secondary Axes are now the same, we can also remove the Secondary Axis labels. Click the Secondary Axis labels and press delete to remove it.

We still have some extra chart lines corresponding to the “Total”, the “Primary Scale”, and the “Secondary Scale”. Starting with the “Total” line, click the data series in the chart. In the Format ribbon, click Format Selection. On the Fill & Line options, select No line.

Repeat the same process for the “Primary Scale” data series line and the “Secondary Scale” data series line.

Change the title to better describe the chart.

Result

We now have a combined clustered and stacked column chart with totals above the stacked column. Our data table remains intact and readable while the helper data and chart elements are hidden from casual viewers. The aggregate total of the stacked column is also displayed above the column.

Excel – Clustered Column Chart With Stacked Column and Data Table
Excel – Clustered Column Chart With Stacked Column and Data Table

Combine Data From Multiple Tables to Create a Clustered Column Chart in Power BI

Clustered column charts in Power BI provide a clean visual comparing multiple data series grouped by category. In this example, I want to create a clustered column chart comparing project spend forecasts against the actual realized spend across several categories. This is a common visual analysis in financial reporting, but it typically requires a fair bit of data wrangling to get the underlying source data into a consumable structure.

The complexity of this example stems from the source data being in two separate tables. For simplicity, I’m using two different Excel worksheets to simulate multiple tables. The Spend Forecast data contains the high-level spend forecast by project and spend category. The Actual Spend data contains the actual costs incurred against a project and spend category. Both tables have the same basic structure, but the information contained in the “AMOUNT” column in each table has a different meaning, i.e., Spend Forecast Amount is different from Actual Spend Amount. Also, the Spend Forecast data is aggregated at an annual level (“YEAR”) while the Actual Spend data is at a specific date level (“POSTED DATE”).

My goal is to create a single table containing both data sets without losing the meaning of their respective “AMOUNT” columns, e.g., a table structured as “YEAR”, “PROJECT NAME”, “SPEND CATEGORY”, “FORECAST AMOUNT”, and “ACTUAL AMOUNT”. The granularity discrepancy between “YEAR” and “POSTED DATE” will also need to be resolved.

Excel – Spend Forecast
Excel – Spend Forecast
Excel – Actual Spend
Excel – Actual Spend

Step 1 – Import Data

As I mentioned earlier, the two data sets are each located on worksheets in an Excel workbook. Starting with a new Power BI report, click Import data from Excel and select the appropriate file. Perform any initial cleanup transformations as needed. The import results in two Power BI tables named FORECAST and ACTUAL containing the Spend Forecast and Actual Spend data respectively.

Power BI Desktop – Imported Data Tables
Power BI Desktop – Imported Data Tables

Step 2 – Create a New Table Using DAX

To create the new combined table, I’ll use DAX functions available in Power BI. The UNION function is used to combine the two tables into one. As part of the UNION, I also use SELECTCOLUMNS to choose the appropriate columns from each individual table.

A combination of real data and the BLANK function are used to assign “AMOUNT” from each table to the newly created “FORECAST AMOUNT” and “ACTUAL AMOUNT” columns. In the FORECAST (Spend Forecast) table, the “ACTUAL AMOUNT” column is blank; in the ACTUAL (Actual Spend) table, the “FORECAST AMOUNT” column is blank.

For the ACTUAL (Actual Spend) table, I use the YEAR function on the “POSTED DATE” column to extract the year portion of the date.

RPT TBL FORECAST VS ACTUAL BY SPEND CATEGORY = 
    UNION(
        SELECTCOLUMNS(
            FORECAST,
            "YEAR",FORECAST[YEAR],
            "PROJECT NAME",FORECAST[PROJECT],
            "SPEND CATEGORY",FORECAST[SPEND CATEGORY],
            "FORECAST AMOUNT",FORECAST[AMOUNT],
            "ACTUAL AMOUNT",BLANK()
        ),
        SELECTCOLUMNS(
            ACTUAL,
            "YEAR",YEAR(ACTUAL[POSTED DATE]),
            "PROJECT NAME",ACTUAL[PROJECT],
            "SPEND CATEGORY",ACTUAL[SPEND CATEGORY],
            "FORECAST AMOUNT",BLANK(),
            "ACTUAL AMOUNT",ACTUAL[AMOUNT]
        )
    )

The following screenshot shows the combined table resulting from the above code. Both the “FORECAST AMOUNT” and “ACTUAL AMOUNT” columns are created and the “YEAR” column contains only the year portion of the underlying dates.

Power BI Desktop – DAX Combined Table
Power BI Desktop – DAX Combined Table

Step 3 – Create a Clustered Column Chart Using the New Table

Add a Clustered Column Chart visualization to the report. Using the newly created table, the Axis is “SPEND CATEGORY” and the Values are “FORECAST AMOUNT” and “ACTUAL AMOUNT”.

Power BI Desktop – Clustered Column Chart Configuration
Power BI Desktop – Clustered Column Chart Configuration

Result

Now that the data is structured as one table with both data series in distinct columns, configuring the Clustered Column Chart is straightforward. Our work produced a simple, yet informative visual comparing forecasted vs. actual spend in each category.

Power BI Desktop – Forecast vs Actual Clustered Column Chart Visualization
Power BI Desktop – Forecast vs Actual Clustered Column Chart Visualization

Alternate Method Using Power Query

As an alternative to using DAX to create the combined table, you could use Power Query instead. I wrote the Power Query version as a single step (formatted for readability) to ease the comparison to the DAX version above. The Power Query version is more complicated as it requires several additional functions to wrangle the data into the equivalent structure and format as the DAX version. If I didn’t want to write it as one step, I would have split this into two queries to individually format the FORECAST (Spend Forecast) and ACTUAL (Actual Spend) tables and then use a third query to combine the results from those individual queries.

= Table.Combine({
    Table.RenameColumns(
        Table.SelectColumns(
            Table.AddColumn(
                #"FORECAST",
                "ACTUAL AMOUNT",
                each null
            ),
            {"YEAR","PROJECT","SPEND CATEGORY","AMOUNT","ACTUAL AMOUNT"}
        ),
        {{"PROJECT","PROJECT NAME"},{"AMOUNT","FORECAST AMOUNT"}}
        ),
    Table.RenameColumns(
        Table.SelectColumns(
            Table.AddColumn(
                Table.TransformColumns(
                    #"ACTUAL",
                    {{"POSTED DATE", Date.Year, Int64.Type}}
                ),
                "FORECAST AMOUNT", each null
            ),
            {"POSTED DATE","PROJECT","SPEND CATEGORY","FORECAST AMOUNT","AMOUNT"}
        ),
        {{"POSTED DATE","YEAR"},{"PROJECT","PROJECT NAME"},{"AMOUNT","ACTUAL AMOUNT"}}
    )
})