Handling null and blank values in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data with some null and blank values in column A

Cell	Value
A1	Sales
A2	100
A3
A4	200
A5
A6	150

Formula Trace

TotalSales = SUMX(FILTER(Table1, NOT(ISBLANK(Table1[Sales]))), Table1[Sales])

Step 1: FILTER(Table1, NOT(ISBLANK(Table1[Sales])))

Step 2: SUMX(filtered rows, Table1[Sales])

Step 3: TotalSales

Cell Reference Map

   A   
1 Sales 
2 100  
3 null 
4 200  
5 ""   
6 150  

Filter removes row 5 (blank), null in row 3 is not removed by ISBLANK

The formula references the Sales column in rows 2 to 6, filtering out blank values

Result

   A       B         
1 Sales   TotalSales 
2 100     450        
3 null               
4 200               
5 ""                
6 150               

TotalSales calculated as 450 ignoring blank Sales values

Sheet Trace Quiz - 3 Questions

Which rows are included in the SUMX calculation?

A All rows including null and blank

B Rows with Sales 100, 200, and 150

C Only rows with null or blank

D Only the first row

Key Result

Use FILTER with NOT(ISBLANK()) to exclude blank values before aggregation