SUMX and IF Measure Returning a Total of 0 in Power BI

[Chateauunoirr]

Hi Power BI Community,

I'm facing an issue with a measure I created in Power BI, and I’d appreciate your help. I’ve built a measure using SUMX combined with IF conditions to calculate values based on specific criteria. While the individual rows in my table show non-zero values, the total for the column displays as 0, which doesn’t seem correct since the sum of the rows should not be zero.

Here’s a brief overview of my measure (simplified) :

Comissions = 
var currentsales = max(Table_Contrats[OwnerId])
var _TableComission =
FILTER(
    ALL('Données comissions'),
    "filtering criteria"
)
RETURN
SUMX(
        _TableComission,
    IF(
        [_prevCumulativeAmount]=[Cumulative Amount],
        0,
        IF(
            Condition1,
            Value1,
            IF(
                Condition2,
                Value2,
.
.
.
    )
)

The result 

Thanks in advance for your help!

[v-pnaroju-msft]

Thankyou, @MFelix, for your response.

Hi @Chateauunoirr,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, when you use the SUMX function over a filtered table with nested IF conditions, Power BI evaluates totals differently. The filter context is missing at the total level, which causes the comparison between  [_prevCumulativeAmount] and [Cumulative Amount] to evaluate differently than expected or to return a blank value.

Please find below the DAX expression that may help resolve the issue:

Commissions =
IF(
HASONEVALUE('Table_Contrats'[YourUniqueRowColumn]),

VAR currentsales = MAX('Table_Contrats'[OwnerId])
VAR _TableComission =
FILTER(
ALL('Données comissions'),

)
RETURN
SUMX(
_TableComission,
IF(
[_prevCumulativeAmount] = [Cumulative Amount],
0,
IF(Condition1, Value1,
IF(Condition2, Value2, 0))
)
),

SUMX(
VALUES('Table_Contrats'[ContractId]),
[Commissions]
)
)

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who are facing similar queries.

Thank you.

[Chateauunoirr]

Hi @v-pnaroju-msft , thank you very much for taking the time to help me!

Could you explain what the HASONEVALUE() function does because I don't really have a unique row:

I have my user table linked to my contracts table on the Owner ID field. This is where I get the sales ID from. (HASONEVALUE('Table_Contrats'[YourUniqueRowColumn]

var currentsales = max(Table_Contrats[OwnerId])

[MFelix]

Hi @Chateauunoirr,

This is related with the way you are calculationg the values and the context that is passed, you should pass the context of the columns you are using (Name, Annee e Mois) to the SUMX in order to get the correct value to this calculation otherwise it will consider the entire table and the result is different.

[Chateauunoirr]

Hi @MFelix , thanks for your reply.
So does that mean I have to put

var currentsales = max(Table_Contrats[OwnerId])
var _TableComission =
FILTER(
    ALL('Données comissions'),
    "filtering criteria"
)

inside the SUMX? That is to say

Comissions = 
SUMX(
        var currentsales = max(Table_Contrats[OwnerId])
        var _TableComission =
        FILTER(
            ALL('Données comissions'),
            "filtering criteria"
        )
        RETURN
        _TableComission,
    IF(
        [_prevCumulativeAmount]=[Cumulative Amount],
        0,
        IF(
            Condition1,
            Value1,
            IF(
                Condition2,
                Value2,
.
.
.
    )
)

[MFelix · Accepted solution]

Hi @Chateauunoirr 

What I mean is that you should do a measure similar to this one:

Comission final =
SUMX(
SUMMARIZE (Table_Contrats, Table_Contrats[Name], Calendar[Année], Calendar[Mois]), [Comissions])

I'm assuming the month and the year are coming from a calendar table.

[Chateauunoirr]

Hello @MFelix , it works like a charm ! 

Thank you very much for your help!

Honestly, I'm having trouble understanding the logic. Why recreate the same table in the final commission measurement, when that's exactly what I have in my matrix?

[MFelix]

Hi @Chateauunoirr ,

This is related with context when you are at the level of the rows the context is the name, year and month so the IF statment works properly, however when you are at the level of the total row the name, year and month are no longer in the context  and you are getting the values from the full table or filtered data,

This means that when you evaluate the if statment it will not consider the values line by line but all of the lines. Making this additional step you will force the calculation to have the correct context because even at the total level you are only considering the name, year, month. 

You will have a table like this:

Name	Year	Month	Value
a	2025	Jan	501
a	2025	Feb
a	2025	Mar	2472
...	...	...	...
a	2025	Dec

So then you make the sum of the value.

[Chateauunoirr]

@MFelix ok thanks for the explanation. Have a nice day