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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Fowmy
Super User
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

 

Comp Sales-1.PNG

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

1 ACCEPTED SOLUTION

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
themistoklis
Community Champion
Community Champion

@Fowmy

 

Try the following:

 

SUMX (
                VALUES ( TableName[STORE] );
                CALCULATE ( SUM ( TableName[Comp Sales] ) )
            )
Greg_Deckler
Super User
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.