How to create a side-by-side comparison visual in Power BI?

This post walks you through how to make a side-by-side comparison visual like the one below using the matrix visual in Power BI. 

Before we dive in, let me briefly talk about the dataset I’m using. This is a synthetic star schema dataset generated by Python. You can access the script as well as the entire dataset on my GitHub. Here is a simplified data model diagram to show the structure. We’re going to use it to create our visual where the user can select any two countries to compare. 

After loading the data model into Power BI, we can begin building the visual. 

Step 1: Create a disconnected, static table for the metrics of interest

This table has two columns: the first column is the name of the metrics you want to compare, and the second column is the order you want these metrics to be shown in your visual. Here is the DAX code to create the table:

comparison_table = DATATABLE(

    "metric", STRING,

    "sort_order", INTEGER,

    {

        {"💲 Doctor Visit Cost", 1},

        {"⚠️ Total Health Risk Score", 2},

        {"🚁 Evacuation Included", 3},

        {"💰 Avg Plan Cost (monthly)", 4}

    }

)

Step 2: Add two country slicers

We will duplicate our dim_country table twice by creating two new tables. Make sure they’re disconnected. Here is the DAX

compare_country_1 = dim_country

compare_country_2 = dim_country

Then we can add two slicers: one from compare_country_1, one from compare_country_2. Each slicer will control a different column in our matrix visual. 

Step 3: Create measures for each selected country

We will create a dynamic measure to return the metric value for each selected country. Let’s start with country 1 by creating a measure in the compare_country_1 table. Here is the DAX:

metric_value_1 =

VAR country = SELECTEDVALUE(compare_country_1[id])

VAR metric = SELECTEDVALUE(comparison_table[metric])


VAR doctor_visit = CALCULATE(AVERAGE('fact'[avg_doctor_visit_cost]), 'fact'[country_id]=country)


VAR plan_cost = CALCULATE(AVERAGE('fact'[avg_plan_monthly_cost]), 'fact'[country_id]=country)


VAR risk_score = CALCULATE(AVERAGE('fact'[normalized_total_risk_score]), 'fact'[country_id]=country)

VAR risk_symbol =

SWITCH(

    TRUE(),

    risk_score<=1.0, "✅",

    risk_score<=2.0, "⚠️",

    "❌"

)


VAR evacuation = CALCULATE(AVERAGE('fact'[include_evacuation]), 'fact'[country_id]=country)

VAR evacuation_symbol =

SWITCH(

    TRUE(),

    evacuation=1.0, "✅",

    "❌"

)


RETURN

    SWITCH(

        metric,

        "💲 Doctor Visit Cost", FORMAT(doctor_visit, "$#,##0.00"),

        "⚠️ Total Health Risk Score", risk_symbol & " " & FORMAT(risk_score, "#,##0.00"),

        "🚁 Evacuation Included", evacuation_symbol,

        "💰 Avg Plan Cost (monthly)", FORMAT(plan_cost, "$#,##0.00"),

        BLANK()

    )

As you can see, we also included logic for the ✅/⚠️/❌ icons to make the visual instantly readable. Now repeat this for metric_value_2 using the compare_country_2 table. This is the only line you need to change: VAR country = SELECTEDVALUE(compare_country_2[id]).

Step 4: Build the matrix visual

In the Matrix visual, add the metric column from the comparison_table which we created in Step 1 to Field Rows. Add both metric_value_1 and metric_value_2 we created in Step 3 to Field Values.

Then I set the font color of both Column headers and Row headers to my background color (which is white in my case), so they become invisible. I also send my matrix visual back.

Last, I move the two slicers over the matrix visual and align them to make them appear as if they're the column headers. 

Step 5: Sort the metric column in user-defined order

Go to the comparison_table, click on the metric column first, then sort it by the sort_order column we created in Step 1. 

This is it — we’ve created a side-by-side comparison visual in Power BI! You can access the .pbix file on my GitHub.