topic Total not equal to rows gotten by if statement DAX in DAX Commands and Tips

Total not equal to rows gotten by if statement DAX

Justas — Tue, 10 Mar 2020 09:22:28 GMT

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

Re: Total not equal to rows gotten by if statement DAX

Anonymous — Tue, 10 Mar 2020 10:47:38 GMT

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

Best
D

Re: Total not equal to rows gotten by if statement DAX

Nishantjain — Wed, 11 Mar 2020 07:54:11 GMT

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