Total incorrect when using IF Statement in column

Hi HYEasterly ,

This is a common issue that arises from the way calculation engines like Power BI's DAX handle totals. The discrepancy occurs because the formula is evaluated differently for the individual rows than it is for the grand total line. For each specific week in your report, the formula operates in a "row context," correctly checking if that single week's [Labor_total] is zero and returning the appropriate value.

However, when calculating the grand total, the formula works in a "filter context" that sees all the weeks at once. It evaluates the total of [Labor_total] for the entire period, which is 10,362.72. Since this total isn't zero, the IF statement simply returns this sum and never considers the [lab_avg] measure that was substituted on the individual rows.

To resolve this, you must force the calculation engine to perform the IF logic on a row-by-row basis first and then sum the results. This is achieved by using an iterator function like SUMX. This function iterates through a table you specify, applies an expression (your IF statement) to each row, and finally aggregates the outcome of each expression. This ensures the total is a true sum of the visible row values, including those where the average was substituted.

You should replace your existing formula with the following measure. This SUMX expression will correctly calculate both the individual lines and the grand total.

Labor = 
SUMX(
    VALUES('Date'[Week]),  // Or the column that represents your weeks
    IF(
        [Labor_total] <> 0,
        [Labor_total],
        [lab_avg]
    )
)

In this formula, you need to replace 'Date'[Week] with the actual table and column from your data model that contains the weekly values you are reporting on. The VALUES function provides SUMX with a distinct list of the weeks visible in the report. By using this pattern, the measure will first evaluate each week, returning 10,362.72 for the first week and 11,408.80 (the average) for the second. Then, SUMX will add these results together to produce the correct total of 21,771.52.