Total not equal to rows gotten by if statement DAX

[Justas]

Hello to everybody,

Problem:

Grand Total not equal to rows gotten by if statement.

Dax for Column "Sutarčių vertė":

Sutarčių vertė =

Var KPIbyCondition = if(or([Projekto vertė PTD]=0;ISBLANK([Projekto vertė PTD]));[Pagrindinės veiklos pajamos];[Projekto vertė])

Return

CALCULATE(KPIbyCondition;ALLSELECTED(Sutartys[Sutarties Nr.2]))

Thank you for your help

[Nishantjain · Accepted solution]

This is a very common issue that you face in DAX. The grand total in DAX doesn't work like excel where it would go and add the values in the column. 

Each cell is calculated independently by DAX, including the grand total value. 

The result from the dax formula you wrote is correct. To understand, you need to look at the calcuation in the grand total line.

As per the fomula, if [Projekto vertė PTD] is null or blank, give [Pagrindinės veiklos pajamos] else  [Projekto vertė PTD]. 

Now the value for [Projekto vertė PTD] for the grand total line is 561,537.00. It is not blank or null, therefore, the result of this dax formula will be 561,537. and not 430.568 as you expect

To overcome this issue, you need to use SUMX to make sure that the grand total adds up to the individual cells. 

An example of that would be 

sumx([Table], if(or([Projekto vertė PTD]=0;ISBLANK([Projekto vertė PTD]));[Pagrindinės veiklos pajamos];[Projekto vertė])

Hope this helps

[Anonymous]

Variables are STATIC. Using them in CALCULATE does not change their value.

Best
D