How to Show 0 Instead of Blank in a Power BI Matrix

A colleague of mine built a sales matrix in Power BI with products on the rows and years across the columns. Two cells were empty, and his manager’s first question was whether the data was missing. It wasn’t: those products simply sold nothing that year, and a 0 would have said so much more clearly.

In this tutorial, I’ll show you three ways to show 0 instead of blank in a Power BI matrix, explain why the cells are blank in the first place, and when you should leave them blank, because Microsoft warns that blank-to-zero measures can slow a report down.

Quick answer: Create a measure such as Quantity (Zero for Blank) = COALESCE(SUM(ProductSales[Quantity]), 0) and use it in the matrix’s Values instead of the column. COALESCE returns the first value that isn’t blank, so empty cells show 0. IF(ISBLANK(...), 0, ...) does the same thing with more typing.

Why the matrix shows blanks

My matrix uses a ProductSales table with Category and Product on the rows, Year on the columns, and the sum of Quantity as the value:

A cell is blank when there are no rows for that combination, for example no Keyboard sales in one year. Power BI shows nothing because there’s nothing to add up. That’s different from a row that exists with an empty Quantity, which matters for Method 3.

If you’re new to the visual, start with how to create a matrix visual in Power BI.

Before you change blanks to 0

Microsoft’s DAX guidance recommends against converting blanks to values in most cases. A measure that returns 0 for every empty cell makes Power BI calculate every possible combination of rows and columns, even ones that will never have data.

On a small matrix, you won’t notice. On a big model with many products and dates, the visual gets slower and uses more memory.

My rule: use zeros when the matrix is small and readers really need to see them. For large tables, leave the blanks and explain them in a note, or use conditional formatting to make them stand out. See Power BI matrix conditional formatting for that.

Method 1: COALESCE in a measure

COALESCE takes a list of values and returns the first one that isn’t blank. Microsoft’s own example uses it to turn blank sales into 0, which is exactly what we need.

1. Select the matrix, then on the Home tab select New measure. The formula bar opens at the top of the canvas.
2. Enter the measure below and press Enter.
3. In the Visualizations pane, remove Sum of Quantity from Values and drag the new measure in.

Quantity (Zero for Blank) = COALESCE(SUM(ProductSales[Quantity]), 0)

Replace ProductSales[Quantity] with your own table and column. The empty cells now show 0, and the totals don’t change, because adding zeros doesn’t change a sum. For more measure patterns, see these Power BI measure examples.

Method 2: IF and ISBLANK

Before COALESCE was added to DAX, the usual way was IF with ISBLANK:

Quantity Zero = IF(ISBLANK(SUM(ProductSales[Quantity])), 0, SUM(ProductSales[Quantity]))

Use it in Values the same way:

It gives the same result, but you write the SUM twice. I only use this form when I need a different value than 0, or a condition, for example showing 0 only for the current year. See Power BI measure IF examples for more.

A visual calculation instead of a measure

If you only need zeros in one visual, you can add a visual calculation. Select the matrix, select New calculation on the Home tab, and enter Quantity or Zero = COALESCE([Sum of Quantity], 0).

Visual calculations work on the data already in the visual and often perform better than a measure. Test it with your data to make sure every empty cell is filled.

Method 3: Replace nulls in Power Query

If your table has rows where Quantity is empty, you can replace those nulls with 0 before the data reaches the report. On the Home tab, select Transform data:

In Power Query Editor, select the table and the Quantity column, then select Replace Values on the Home tab:

Enter null in Value To Find and 0 in Replace With, select OK, then Close & Apply:

Two cautions. This only fixes rows that exist with an empty value; it can’t create a row for a product that sold nothing, so the matrix can still show blanks. And it changes your data: an average or a count of Quantity now includes those zeros.

For the difference between the two tools, see Power Query vs Power BI.

Show rows and columns that have no data

Sometimes the problem isn’t a blank cell but a missing row: a product with no sales at all disappears from the matrix. To keep it, select the arrow next to the field in Rows or Columns and select Show items with no data. Combined with Method 1, those rows then show 0.

This option has the same performance cost, so use it on small dimensions like Category rather than on dates or customers. For similar matrix settings, see how to show all values in a Power BI matrix.

Troubleshooting

• Some cells are still blank: you used Power Query, which can’t fill missing combinations. Use the COALESCE measure.
• The matrix got slow: the zero measure forces every combination. Remove it or reduce the fields on the rows.
• Totals look wrong after Power Query: averages and counts now include the new zeros. Use a measure instead.
• You want to hide totals instead: see how to turn off grand totals in a Power BI matrix.

These Power BI matrix tutorials go well with this one:

• Calculate Percentage of Total in Power BI Matrix
• Display Average Instead of Total in Power BI Matrix Visual
• Power BI Matrix: Hide Column Headers, a Column or Blank Columns
• How to Set Column Width in Power BI Matrix Visual?

Frequently Asked Questions