Total incorrect when using IF Statement in column

[HYEasterly]

I will start with saying that my data is very complicated.  So I'm not sure if I can give enough of the criteria to get an answer... but I will try.

My report has weekly amounts.  If the data is not available then we want it to return the amount from another table... the "Average Table".  I have figured out how to get the rows to include the average amount.  BUT the total does not include it...

This is the formula.  Both [Labor_total] & [lab_avg] are measures.

Labor = IF(

            [Labor_total]<>0,

            [Labor_total],

            [lab_avg])

This is the result I get:

The yellow highlighted number is the average.

The desired total in this case would be 21,771.52.

What do I need to do to have the total include this amount?

Thank you in advance!

HYEasterly

[HYEasterly]

@DataNinja777 
I want to thank you for the explanation along with the formula fix!!  I like to understand how things work... it helps me down the road when faced with a similar issue.  Your explanation was very detailed and the new formula worked!

[Nasif_Azam]

Hey @HYEasterly ,

It seems like you're trying to calculate the total for your "Labor" column while including the average value in cases where the data is not available. However, the issue is that your total isn't including the calculated values properly when averages are involved. In Power BI, measures behave differently from regular columns, especially when you're aggregating them. To ensure the total reflects the correct sum, including the average values, you'll need to modify the approach for handling the total.

Use SUMX to manually aggregate the rows and include the average values where necessary.

Labor = 
SUMX(
    'YourTableName', 
    IF(
        [Labor_total] <> 0, 
        [Labor_total], 
        [lab_avg]
    )
)

This should calculate the total correctly, as it evaluates each row individually and sums them up, taking into account the conditional logic you've applied for averages.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[mark_endicott]

@HYEasterly - Try something like this to start:

IF (
    ISINSCOPE ( 'Table'[Column] ),
    IF ( [Labor_total] <> 0, [Labor_total], [lab_avg] ),
    [Labor_total] + [lab_avg]
)

in this you need to replace 'Table'[Column] with the column in your visual. 

If this gives an incorrect value, I'll need much more information about your model. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[DataNinja777 · Accepted solution]

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.