Power BI

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

A common dashboard requirement is to take a set of text-based values and visually represent them as colorful icons in Table or Card visualizations. With tools like Microsoft Power BI or TIBCO Spotfire, it is fairly simple to map specific data values or data ranges to a set of default icons through conditional formatting. However, if you want to display an icon in a Card visualization in Power BI, then it’s a slightly more complicated process using a combination of DAX (Data Analysis Expressions) measures and Unicode characters to produce the expected result.

The following image illustrates the expected result using two different methods. The “STATUS” column uses the default icons available when conditionally formatting a column in a Table visualization. The “MEASURE STATUS ICON” column displays the Unicode character output from a DAX measure which is then colored using another DAX measure and conditional formatting. I find the DAX method to be more useful since it may also be used to format other visualizations such as the Card visualization as shown in the second image.

I’ll demonstrate both methods, but the DAX method is the primary focus of this example.

Step 1 – Create Underlying Data

To demonstrate this functionality, I have a list of Project Names and their associated Status listed in an Excel worksheet. The set of valid Status values include “Not Started”, “On Track”, “At Risk”, “Off Track”, and “Complete”.

Step 2 – Import Data

Starting with a new report in Power BI, the Excel file containing the Project Name and Status list worksheet is imported. Click Import data from Excel and select the appropriate file.

Step 3 – Transform Data

As part of the import process, the data may need to be transformed if the column headings are not recognized. In the screenshot below, the columns are labeled “Column1” and “Column2” instead of “PROJECT NAME” and “STATUS”. Click Transform Data.

The Power Query editor opens. As part of the Applied Steps, click Use First Row as Headers on the File ribbon to promote the first row as the new column headings.

Step 4 – Convert Text Values to Numbers

In this step, the text-based Status values are mapped to numeric values. This mapping eases both the conditional formatting configuration as well as the DAX measures which are created in later steps. Also, from a maintenance perspective, it reduces the number of locations where hard coded text values need to be modified when someone eventually asks to rename the values.

Switch to the Data view and then click New column on the Table tools ribbon.

Using a Switch function, the text-based Status values are mapped to numeric values. I chose increments of 100, but you may choose any unique numeric integers. The new column is named “STATUS NUMERIC”.

• Not Started = 100
• On Track = 200
• At Risk = 300
• Off Track = 400
• Complete = 500

STATUS NUMERIC = SWITCH([STATUS],"Not Started", 100, "On Track", 200, "At Risk", 300, "Off Track", 400, "Complete", 500)

Step 5 – Create DAX Measures

Now that we have numeric values mapped in the “STATUS NUMERIC” column, those values can be used by two new DAX measures to map to Unicode characters as well as hex color codes.

On the Home ribbon, click New measure and add the following code to create the icon measure. Repeat for the process to create the icon color measure.

The numbers used in the UNICHAR function correspond to the equivalent HTML codes for a Figure Dash (8210), Black Large Circle (11044), Black Up-Pointing Triangle (9650), Black Diamond (9670), and White Circle (9675).

MEASURE STATUS ICON = 
    VAR vStatusNumeric = SELECTEDVALUE('PROJECT DATA'[STATUS NUMERIC])
    RETURN
        SWITCH(
            TRUE(),
            vStatusNumeric = 100, UNICHAR(8210),
            vStatusNumeric = 200, UNICHAR(11044),
            vStatusNumeric = 300, UNICHAR(9650),
            vStatusNumeric = 400, UNICHAR(9670),
            vStatusNumeric = 500, UNICHAR(9675)
        )

In simplified terms, the hex color codes correspond to black, green, yellow, and red (although these are not the equivalent of the HTML color names green, yellow, and red). I have also excluded the 500 value since the corresponding Unicode character is, by default, in the color I expect.

MEASURE STATUS ICON COLOR = 
    VAR vStatusNumeric = SELECTEDVALUE('PROJECT DATA'[STATUS NUMERIC])
    RETURN
        SWITCH(
            TRUE(),
            vStatusNumeric = 100, "#000000",
            vStatusNumeric = 200, "#84C28A",
            vStatusNumeric = 300, "#F9D087",
            vStatusNumeric = 400, "#F78272"
        )

Step 6 – Create Table Visualization

Switch to the Report view and add a Table visualization. Add the “PROJECT NAME”, “STATUS”, and “MEASURE STATUS ICON” fields as Values in the Table visualization. In subsequent steps, we’ll add default icons to the “STATUS” column using basic conditional formatting and color to the “MEASURE STATUS ICON” using a DAX measure. As illustrated in the screenshot, the DAX measure mapping numeric status to Unicode character is working.

Step 7 – Conditional Formatting for Default Icons in Table Visualization

Click on the “STATUS” field in the list of Values for the Table visualization. Select Conditional formatting and then Icons. Follow the screenshot below to configure the formatting of the “STATUS” field using the numeric values defined earlier. This configuration formats the column to show icons immediately to the left of the text values in the STATUS column in the Table visualization.

After applying conditional formatting, the Table visualization now displays icons in the STATUS column.

Step 8 – Conditional Formatting for Unicode Characters in Table Visualization Using DAX Measures

In this step, we’ll add color to the Unicode character icons. Click on the “MEASURE STATUS ICON” field in the list of Values for the Table visualization. Select Conditional formatting and then Font color. Follow the screenshot below to configure the formatting of the “MEASURE STATUS ICON” field using the DAX measure that maps numeric status to a defined color.

Now the Table visualization is fully formatted, as expected, using both methods.

Step 9 – Create Card Visualization

While in Report view, add a Card visualization. Add the “MEASURE STATUS ICON” field to the visualization Values. In subsequent steps, we’ll add default icons to the “STATUS” column using basic conditional formatting and color to the “MEASURE STATUS ICON” using a DAX measure. As illustrated in the screenshot, the DAX measure which maps numeric status values to Unicode characters is working.

Step 10 – Conditional Formatting for Unicode Characters in Card Visualization Using DAX Measures

Click on the added Card visualization and go to its Format options. Find the options for Data label and click the fx button for Color. Configure the options as illustrated below.

Result

Now that everything is configured, both the Table and Card visualizations are working as expected. The Unicode character icons are displayed and colored in both visualizations. If a specific entry in the Table visualization is selected, then the Card visualization displays the corresponding Unicode character with the appropriate color.

While there are other methods of introducing custom icons into a report, using DAX measures to introduce Unicode character icons is a fairly simple and lightweight method with a wide variety of options.