Handling null and blank values in Power BI - Dashboard Guide

Dashboard Mode - Handling null and blank values

Business Question

How can we accurately calculate total sales and average sales per order when some sales data is missing or blank?

Sample Data

Order ID	Customer	Sales Amount
1001	Alice	250
1002	Bob
1003	Charlie	400
1004	Diana	0
1005	Eva
1006	Frank	150

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUMX(FILTER(Sales, NOT(ISBLANK(Sales[Sales Amount]))), Sales[Sales Amount])
  Result: 800 (250 + 400 + 0 + 150)
• KPI Card: Average Sales per Order (excluding blanks)
  Formula: Average Sales = AVERAGEX(FILTER(Sales, NOT(ISBLANK(Sales[Sales Amount]))), Sales[Sales Amount])
  Result: 200 (800 total sales / 4 orders with sales)
• Table: Sales Details
  Shows all orders with sales amount, including blanks and zeros for transparency.
• Bar Chart: Sales Amount by Customer
  Visualizes sales amounts; blanks treated as zero for display.

Dashboard Layout

+----------------------+----------------------+
|      Total Sales      |   Average Sales      |
|        (KPI)          |       (KPI)          |
+----------------------+----------------------+
|                                              |
|          Sales Amount by Customer (Bar Chart) |
|                                              |
+----------------------------------------------+
|              Sales Details (Table)            |
+----------------------------------------------+

Interactivity

Filter by Customer name or Order ID updates all components. When filtered, Total Sales and Average Sales recalculate ignoring blank sales amounts. The bar chart and table show only filtered data.

Self Check

If you add a filter to show only orders where Sales Amount is not blank, which components update and how?

• Total Sales and Average Sales KPI cards update to exclude blank sales.
• Bar Chart shows only customers with sales amounts.
• Sales Details table shows only orders with sales amounts.

Key Result

Dashboard shows total and average sales handling blanks, with sales details and customer sales chart.