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.

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.

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