cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Super User

## Different Grand Total - Calculate vs SUMX

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
1 ACCEPTED SOLUTION
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Community Champion

@Fowmy

Try the following:

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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

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]
)```
Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...