Measure with condition IF do not summarize

[Anonymous]

Hello colleagues!

I have a measure that has an IF that checks if the sum of 2 other measures are 0, if they are, it brings the value from a third Measure.

If not, then it brings Zero.

It works, however it seems that the IF is applied even for the Total of the measure, so it doesn't bring the Summarization of the values

If I change the IF to 1 in the end, it brings 1 as the Sum:

[Anonymous]

You can try something like the following, though will need to be sure the table, columns, and measure match up to what you actually have:

=
IF(
    HASONEVALUE(Table[Pin]),
    [No Future Usage],
    SUMX(
        VALUES( TABLE[PIN]),[No Future Usage]
    )
)

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

The problem you are seeing with the total row is that it has no concept of the rows in the matrix.  It is calculated as if you took the PN field out of your visual.  What you want to do is add a SUMX to your calculation that will force it to iterate over the PN list.

Something along these lines.

No Future Usage =
SUMX (
    VALUES ( YourTable[PN] );
    IF ( [Requirements] + [ReqsBulk] = 0; [Valid Stock Value]; 0 )
)

This will do the base calculation over all the individual PN then sum the result of each of those.

[Anonymous]

Awesome! It worked as a charm!

Before this, I tried doing it with a CALCULATE but it didn't work:

No future usage = CALCULATE([Valid Stock Value];fRequirements[Quantity]=0)

Can you spot what is wrong here?

[jdbuchanan71]

The SUMX turns the measure into an iterator that runs the calculation against the entire table we fed it 

VALUES ( YourTable[PN] )

Then once it has the calculations for that table it sums the amount.  On an individual row the calculation is for a single PN but on the total row it is the sum of that calc on the table we fed in.  If you don't do it as an interator it runs the total against no PN.