- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Different Grand Total - Calculate vs SUMX

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 06:02 AM

Hi,

I need to some help in understanding the following problem. Comp Sales (Calculate) measures returns incorrect values at total level, the correct amount is -2,180.00 which is produced by the Comp Sales (SUMX.) version I am struggling to find the break of -9,961 using different approaches like CONCATENATEx to know how Calculate behaves at total level but no clues so far.

I am trying to use the Calculate version of the solution as my actual data set is huge.

I have attached the PBIX file. Attachment

Solved! Go to Solution.

Accepted Solutions

## Re: Different Grand Total - Calculate vs SUMX

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 10:41 AM

Right, so CALCULATE is not the way to go here, SUMX is the way to go. If you think about this, in your CALCULATE formula, you are doing a FILTER on the CROSSJOIN of ALL rows in your table with your DATE table, and hence your wrong answer. The way SUMX works is that the first part is that you are creating a table with your CROSSJOIN where your Sales Growth is calculated per line and then you SUM up that column in that resulting table, which is why you get the right answer. Hence why the Measure Totals: The Final Word uses the "X" aggregation functions like SUMX.

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

All Replies

## Re: Different Grand Total - Calculate vs SUMX

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 06:22 AM

Can you post your formula for the measure that is not working correctly? I prefer not to download PBIX files. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

## Re: Different Grand Total - Calculate vs SUMX

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 06:36 AM

Thanks, Greg,

I will have a look at the links you have provided, meanwhile, these are the measures I have used.

Total Sales = SUM(Sales[Sales Amount]) Total Sales LY = CALCULATE([Total Sales],DATEADD('DATE'[Date],-1,YEAR)) Sales Growth = [Total Sales]-[Total Sales LY] Comp Sales (Calculate) = CALCULATE([Sales Growth] ,FILTER( CROSSJOIN(VALUES('DATE'[Date]),Branch) ,'DATE'[Date]>EOMONTH(Branch[Open Date],12) ) ) Comp Sales (SUMX) = SUMX( FILTER( CROSSJOIN(VALUES('DATE'[Date]),Branch) ,'DATE'[Date]>EOMONTH(Branch[Open Date],12) ) ,[Sales Growth] )

## Re: Different Grand Total - Calculate vs SUMX

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 10:41 AM

Right, so CALCULATE is not the way to go here, SUMX is the way to go. If you think about this, in your CALCULATE formula, you are doing a FILTER on the CROSSJOIN of ALL rows in your table with your DATE table, and hence your wrong answer. The way SUMX works is that the first part is that you are creating a table with your CROSSJOIN where your Sales Growth is calculated per line and then you SUM up that column in that resulting table, which is why you get the right answer. Hence why the Measure Totals: The Final Word uses the "X" aggregation functions like SUMX.

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

## Re: Different Grand Total - Calculate vs SUMX

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 11:26 AM

Try the following:

SUMX ( VALUES ( TableName[STORE] ); CALCULATE ( SUM ( TableName[Comp Sales] ) ) )