Why clean data drives accurate reports in Power BI - Formula Trace Breakdown

Sample Data

Sales data with a data quality issue: cell B4 contains text 'abc' instead of a number.

Cell	Value
A1	Product
A2	Apple
A3	Banana
A4	Apple
A5	Orange
B1	Sales
B2	100
B3	150
B4	abc
B5	200

Formula Trace

SUMX(FILTER(SalesData, ISNUMBER(SalesData[Sales])), SalesData[Sales])

Step 1: FILTER(SalesData, ISNUMBER(SalesData[Sales]))

Step 2: SUMX(filtered_rows, SalesData[Sales])

Cell Reference Map

    A       B
1 Product  Sales
2 Apple    100  
3 Banana   150  
4 Apple    abc  
5 Orange   200  

Formula uses Sales column B2:B5 but excludes B4 because it's not a number.

The formula references the Sales column cells B2 to B5, filtering out the non-numeric value in B4.

Result

    A       B       C
1 Product  Sales   Total Sales
2 Apple    100     450
3 Banana   150
4 Apple    abc
5 Orange   200

The Total Sales measure sums only valid numeric sales, ignoring the invalid 'abc' entry, resulting in 450.

Sheet Trace Quiz - 3 Questions

Why does the formula exclude the value 'abc' in cell B4?

A Because 'abc' is the highest sales value

B Because 'abc' is a product name

C Because 'abc' is not a number

D Because 'abc' is a duplicate

Key Result

Filter data to include only valid numeric values before summing to ensure accurate results.