Excel

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.

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).

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.

Step 1 – Import Data

As I mentioned earlier, the two datasets 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.

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.

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”.

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.

Alternate 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"}}
    )
})