Power BI: Create a Filled Donut Chart with DAX and Card Visualizations

Donut charts, second only to pie charts as the tastiest visualization, are used to show the proportions of individual components within a whole. They are similar to pie charts, but with a hollow center, which often contains additional information. In my experience, donut charts are often preferred over pie charts because they offer higher data density (more information in one visualization) and have a more modern look compared to traditional pie charts.

While Power BI includes a donut chart visualization, it lacks an out-of-the-box option to fill the center. However, you can create a filled donut chart by combining Data Analysis Expressions (DAX) measures and card visualizations.

In this example, the donut chart shows spending in different categories. While a default donut chart displays the proportion and magnitude of individual components, the overall total is not visible.

Instructions

Step 1: Create Underlying Data

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

The values are formatted using a custom number format (shown below) to display in millions. For example, if the underlying value is 1,000,000, Excel will display it as 1.0 with this format.

#,##0.0,,;[Red](#,##0.0,,)

Step 2: Import Data

In the example Excel file, the data is on a worksheet named DATA.

1. To create a new Power BI report, click Import data from Excel from within Power BI.
2. Select the Excel data file.
3. Make any necessary cleanup transformations.
4. The import creates a new Power BI table named Table 1 (DATA), which I have renamed to the more informative DATA.

Step 3: Add Helper Column

As mentioned earlier, we only want the donut chart to display entries related to actual spend. To do this, we add a column that will help filter the data in later steps. In the example data, rows with actual spend are conveniently prefixed with ACTUAL – , making the logic straightforward. In this snippet, the new column will show ACTUAL if the Category column starts with ACTUAL – . All other rows are labeled as OTHER. For simplicity, I use the LEN() function instead of counting the characters in the prefix.

1. In Power BI, select the DATA table from the Data pane.
2. From the Table tools ribbon, click New column.
3. Add the following code to create the new helper column.

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

The data table now appears (below) with the helper column added. The relevant rows are tagged with ACTUAL in the new column.

Step 4: Create DAX Measures

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

For each of the three new measures:

1. Select the DATA table from the Data pane.
2. From the Table tools ribbon, click New measure.
3. Add the corresponding code below to create the new measure.

The first measure calculates the total of all actual spend entries. Since the sample data already includes a total row, this is straightforward. We simply look for the entry where the Category is Total. To ensure the measure ignores any other filters, we use the ALL function within the FILTER of the CALCULATE function, which returns 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. The ALL function is not used here because we only want to include entries tagged as ACTUAL in the helper column. This measure should also respond to user selections and filters in the donut chart visualization. For example, if a user selects a category, the center calculation will adjust accordingly.

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

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

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 Data pane under the DATA table. Then, go to the Measure tools ribbon and set the Format to Percentage. Also, adjust the decimal places to 1.

Step 5: Create Donut Chart

1. Switch to the Report view.
2. Add a Donut chart visualization.
3. From the DATA table, use the Category field for Details and the Total field for Values. The donut chart will display all entries, so we’ll need to use the helper column added earlier. In the Filters pane, add the COLUMN_ACTUAL_CATEGORY helper column and select only the entries tagged as ACTUAL.

In the Format visual settings under Effects, turn off the Background. Then, in the Detail labels section under Values, set the Value decimal places to 1.

NOTE: Turning off the background color is important to make the center of the donut chart transparent, allowing the additional information to be visible.

Step 6: Create Card Visualizations

We need two card visualizations to fill the center of the donut chart.

Insert a new Card visualization. The first card will display the second DAX measure created in Step 4, MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM, which shows the total of all selected and filtered elements from the donut chart. Add MEASURE_SUM_FILTERED_ACTUAL_SPEND_USING_SUM to the Fields for this card.

In the Format visual settings, turn off the Category label. In the Callout value section, set the Value decimal places to 1 and turn on Bold font formatting.

Insert a new Card visualization. The second card will display the third DAX measure from Step 4, MEASURE_PERCENT_OF_TOTAL, which shows the percentage of the selected and filtered entries in the donut chart against the total actual spend. Add MEASURE_PERCENT_OF_TOTAL to the Fields for this card.

In the Format visual settings, turn off the Category label. In the Callout value section, set the Value decimal places to 1.

Step 7: Formatting and Clean-up

Now that all the components are created, it’s time to assemble them into the final product. Align the two cards in the center of the donut chart. Adjust the sizes of the donut chart and cards, along with their fonts, until everything looks right. Bring the donut chart to the front or send the cards to the back so their corners don’t interfere with the chart.

Result

The final chart displays the calculations in the center of the donut chart as expected.

If the user selects or filters one or more segments of the donut chart, the center cards and DAX measures are recalculated to reflect those selections (screenshot below).

Summary

This guide demonstrates how to create a filled donut chart in Power BI using DAX measures and card visualizations. By structuring the data, adding necessary filters, and calculating totals and percentages, we can display key information in the center of the donut chart. The final chart dynamically updates based on user selections, providing an interactive and informative visualization.