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.
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,,)
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”.
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.
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.
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”.
In the Format settings, turn off Background and in the Detail labels section change the Value decimal places to 1.
NOTE: 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.
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.
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.
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).
thank you for the information