Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Justas
Advocate I
Advocate I

Total not equal to rows gotten by if statement DAX

 

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]))
 
Total not equal to rows gotten by if statement.jpg
Thank you for your help
 
 
 

 

 

 

1 ACCEPTED SOLUTION
Nishantjain
Continued Contributor
Continued Contributor

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

View solution in original post

2 REPLIES 2
Nishantjain
Continued Contributor
Continued Contributor

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
Not applicable

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

Best
D

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.