subtracting 2 columns with IF gets me diferent result

[Drako]

Hi,

   I have 2 columns. Estimated and Completed. I created a new column that substracts these two. This works fine. I then added a IF statement and the calulations are wrong.

Correct result without IF: 

Estimated-Completed= [CostOriginalEstimate]-[CostCompletedWork]

With IF: 

Estimated-Completed with IF= IF([CostOriginalEstimate]-[CostCompletedWork]>=0,0,[CostOriginalEstimate]-[CostCompletedWork])

I have been reading about the context of the table but still I don't udnertand why a simple IF and substraction doesn't work and how to fix it.

[MFelix]

Hi @Drako ,

Has you refer measures are based on context this means that the calculations are made based on the column, filters, rows that you use on your visualizations. Making this on a practic way if you have the following values:

Cat	Value
A	10
B	20
C	30
D	40
E	50

And you want to sum all the values that are greater than 20 and you do the following measure:

Values Above 20 = IF(SUM('Table'[Value]) <= 20 ; 0 ; SUM('Table'[Value]))

The final result is this one:

What is happening is that the calculation is picking up the sum for the all values within context and comparing them to 20 so for CAT A and B the sum is lower or equal to 20 it presents 0 for the rest of the CAT return that value. However when you go to the total what is happening is that the SUM(Table[Value]) is calculating first the sum of all the categories  ( A + B + C + D +E) because the context of this is the full table and returns 150 instead of return 120 that should be the correct result, in this case we need to use a sumx to go over the total if I redo the measure to:

Values Above 20 after =
SUMX (
    VALUES ( 'Table'[Cat] );
    CALCULATE ( IF ( SUM ( 'Table'[Value] ) <= 20; 0; SUM ( 'Table'[Value] ) ) )
)

The final result will be the following:

Has you can see I have overwritten the context of the measure and in the total the value is now correct.

(The formula above is not the best example but is just for example purposes).

Looking at you case you need to chan ge the context of your calculation however the measures that you are calculating have an impact on that calculation alonside with the row values.

I tried to replicate your error but was not abble again because of context.

Can you please share a mockup data or sample of your PBIX file and expected result. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Drako]

Hi @MFelix 

    I'm actually not using measure but columns that I created in the DAX. Not sure if it also applies here? 
    How can I send you in an easy way  PBIX without all the compelxity of the data and tables in the file. Is their a way to export just the data of the the table and then share this with you.... Sorry I have not shared a PBIX file due to confidenciality...
    I just don't understand why a simple subtract doesn't work... I use TABLEAU with other cusotmers and never have these complexities with Totals and simpel calculations.   😞

Kind Regards

Lucas Sain

[MFelix · Accepted solution]

Hello @Drako ,

If it is a calculated column, this is also applied in a different way again due to context and aggregation.

Suppose you have the following values:

cat	Cost	Value	Cost - Value	If the cost
A	10	20	-10	-10
A	15	10	5	0
A	20	15	5	0

If you have calculated these values in a table, you can see that the total has the following:

Cost - Value 0

If the cost is -10

This is at the aggression level because when you collect the values you have a sum of individual lines, the cost and value columns do not match the IF Cost.

In the IF you are removing everything that has a value above 0 however in the cost and value columns you are considering all the values so different results.

In this case for the calculation to be correct and return 0 it is necessary to create a measure with the following syntax:

Measure = 
SUMX (
    VALUES ( 'Table'[CAT] ),
    CALCULATE (
        IF (
            SUM ( 'Table'[CostOriginalEstimate] ) - SUM ( 'Table'[CostOriginalEstimate] ) >= 0,
            0,
            SUM ( 'Table'[CostOriginalEstimate] ) - SUM ( 'Table'[CostOriginalEstimate] )
        )
    )
)

This should give the expected result in my case 0:

[Drako]

Hi @MFelix  Miguel,

    Thnaks for all your feedback.  I got it to work. The curious thing I have learned the hard way is that I was doing all this in a calculated column... so it was never working...it was giving these odd results...once I passed this to a measure it worked!!! 

   I actually was not able to ge tthe total to sum in my Matrix si I added a little change:

FinancialDeviationActual =

var deviation = sum('table'[CostOriginalEstimate])-sum(table'[CostCompletedWork])

var result =

if(ISFILTERED('table'[Epic Name]),

      SUMx(

      Values('table'[ProjectSK]),

      CALCULATE(if(deviation >=0,0,deviation ) )

  ),

     SUM('table'[CostOriginalEstimate]) - SUM('table'[CostCompletedWork])
)

return result

    Thanks!!!!

[MFelix]

Hi @Drako

I did not refered that because I forgot since you have 2 levels in the matrix the calculation needs to have that additional syntax glad you were abble to figure it out.