Question

How can I build a Power BI matrix to compare Plan and Fact values side-by-side even if some values are missing?
I’m trying to build a matrix in Power BI that compares Plan and Fact values side-by-side, but some values are missing in the dataset. I need to know how to set up the matrix so that it displays both Plan and Fact values, even if some are missing, without breaking the comparison.

Answer 1

To build a Power BI matrix that compares Plan and Fact values side-by-side, even when some values are missing in the dataset, you can follow these steps:

1. Ensure Proper Data Structure

• You should have two columns or tables representing Plan and Fact data. If they are in separate tables, make sure they are properly related, typically through a shared key (like a Date or Product ID).

• If Plan and Fact data are in the same table but in separate columns, you can directly work with them in the matrix.

2. Handle Missing Values with DAX

• If some values are missing in either the Plan or Fact columns, you can use DAX to replace those missing values with 0 (or another default value) to ensure they appear in the matrix without breaking the comparison.

Example:

Plan Value = COALESCE(SUM('Plan'[Value]), 0)
Fact Value = COALESCE(SUM('Fact'[Value]), 0)

• This will ensure that if there are missing values for either Plan or Fact, the matrix will show 0 (or another default value) instead of leaving an empty cell.

3. Create the Matrix Visual

• Drag a Matrix visual onto your report canvas.

• Place the rows (e.g., Time Period, Product, or Category) in the Rows field well.

• Place the Plan Value and Fact Value measures (created using DAX) in the Values field well.

4. Use “Show Items with No Data” Option

• In the case where rows exist in the dataset (e.g., time periods or products) but the corresponding Plan or Fact value is missing, you need to ensure the missing data rows are still shown in the matrix.

• To do this:

  1. Right-click on the Rows field (e.g., Date or Product) in the Fields pane.

  2. Choose Show Items with No Data. This will ensure that all rows are displayed, even if there is no corresponding data in either the Plan or Fact columns.

5. Customizing for Readability

• Conditional Formatting: You can apply conditional formatting to make it clear which cells contain Plan values, Fact values, or missing values. This can help highlight discrepancies or missing data.

• Column Headers: Rename the columns in the matrix to clearly distinguish Plan from Fact, for example, “Plan Value” and “Actual Value”.

• Sorting: Sort the matrix by a relevant dimension (e.g., Time) to make the comparison clearer.

6. Handle Missing Data for Comparison

• If you have missing values for both Plan and Fact for certain rows, consider showing the comparison as null/empty or using a DAX expression like IF(ISBLANK()) to highlight missing data, such as:

Plan vs Fact = 
IF(ISBLANK([Plan Value]) || ISBLANK([Fact Value]), "Data Missing", [Plan Value] - [Fact Value])

This will label the row as "Data Missing" where either Plan or Fact data is unavailable.