SUM vs SUMX What's The Real Difference Between These DAX Measures

[@EnterpriseDNA]

***** Learning Power BI? ***** FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-reference-guide-download FREE - Power BI Resources - enterprisedna.co/power-bi-resources Enterprise DNA Membership - enterprisedna.co/membership Enterprise DNA Online - portal.enterprisedna.co/ Enterprise DNA Events - enterprisedna.co/enterprise-dna-events-page/ ***** Related Links ***** Working With Iterating Functions In DAX - blog.enterprisedna.co/working-with-iterating-functions-in-dax/ Using Iterating Functions SUMX And AVERAGEX In Power BI - blog.enterprisedna.co/using-iterating-functions-sumx-and-averagex-in-power-bi/ Power BI Data Model For Advanced Scenario Analysis Using DAX - blog.enterprisedna.co/advanced-scenario-analysis-techniques-for-power-bi-wdax/ ***** Related Course Modules ***** DAX Formula Deep Dives - portal.enterprisedna.co/p/dax-formula-deep-dives Mastering DAX Calculations - portal.enterprisedna.co/p/mastering-dax-calculations Advanced DAX Combinations - portal.enterprisedna.co/p/advanced-dax-combinations ***** Related Support Forum Posts ***** SUMX Vs Row By Row Multiplication - forum.enterprisedna.co/t/sumx-vs-row-by-row-multiplication/5645 Financial Templates Vs Last Year Make Same Period - forum.enterprisedna.co/t/financial-templates-vslastyear-make-sameperiod/2214/14 Removing Context From SUMMARIZE - forum.enterprisedna.co/t/removing-context-from-summarize/4674 For more SUM vs SUMX support queries to review see here - forum.enterprisedna.co/search?q=SUM%20vs%20SUMX

[@tungacta]

Sam, you do a terrific job in explaining the "technical difference", however, in order to understand the "real world" difference, you should also include examples of a few other more powerful features of SUMX. Because, at the end of this particular tutorial, I thought to myself, "oh Ok, so instead of having a Column that is the product of 2 other columns, I could just do this with a measurement SUMX. You demonstrated what could be done with both SUM and SUMX. You did not show, more importantly, What cannot be done with SUM that is possible with the "iterating Engine" utilized with SUMX. Magic here is the Expression option.

[@SG-pf1vn]

This is such a great explanation, would've have been so helpful to me long ago when I started with DAX. I remember one more example I was given though that really cleared things up for me which would be great in this video, contrast this example with one where it's not possible to achieve the result that is typically desired with both functions SUM and SUMX (albeit in this case it with the presence of the revenue data in the physical table) something like a percentage value in a physical column where it will never make sense to add percentages, hence a situation where if you had the percentages in a table for each row (cause that's what you were given for whatever reason) and you wanted a measure to give you a percentage for an aggregate of the two columns that were used to calculate the percentage (assuming they're in the data set) you would have to use Sum to get a result that makes sense. Someone showed me that as an example at one point and I remember that it really clarified completely for me anyway.

[@JC-bx1vt]

I don't understand your example. If you had the columns that were used to calculate the percentage by row, then you could just use SUM, right? For example, say you wanted an aggregate Percent Profit. You've already got a column called PercentProfit, which is Profit divided Revenue for each row. You'd just use SUM to get your aggregate measure: Measure = divide(sum(Profit),sum(Revenue),0) What am I missing?

[@tungacta]

Exactly! Sorry, I wrote my comment before reading yours....

[@SG-pf1vn]

@@JC-bx1vt nothing wrong with that, that'll work too, this however and variations of the basic calculation can also be done with sumx, which is what I was making reference too. Maybe my example not the best as it can be done with a combination of sum and divide.

[@HueMannKind]

Awesome awesome job. This has confused me for a long time, and here's the answer in 7 1/2 minutes!

[@EnterpriseDNA]

Hi Caleb, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

[@midwest042003]

Great job! What if you have to multiply two columns from different tables

[@CFenghuang7]

Thank you! Is it possible to have this calculation done between different tables? I have a table "ShipHistory" with material number and ship Qty, and a Master Data table with material number and different types of cost per unit. I could merge both of the tables then do the Sumx, but I was hoping there is a simpler way to just pull data from different tables and calculate it.

[@EnterpriseDNA]

Hi shifeng li, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful! You can subscribe to our KZbin channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

[@vinodjadhav7334]

Hi sir.Where i can find these data sources.

[@vedsoh]

is there a differenceX too? I want to find difference between creation date and completion date for all the inputs and wanted to do it with measures instead of simply doing it with custom column.

[@simonsaruggia]

This is awesome! thank you

[@EnterpriseDNA]

Hi Simone, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming tutorials and announcements. Cheers!

[@Renatofsa355]

Awesome explanation

[@EnterpriseDNA]

Hi Renato, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

[@PeachBeef]

Fantastic clarification

[@henrytnh1]

Many thanks. Very articulate explanation.

[@EnterpriseDNA]

Hi Henry Tnh, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@cblondhe]

Well Explained. Thanks

[@EnterpriseDNA]

Hi Chandra, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

[@mohammadrizeg]

Great efforts to educate us carefully, thanks alot, Sam.

[@EnterpriseDNA]

Hi Mohammed, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@davidh5573]

Made sense when you explained it!! Thank you!!

[@EnterpriseDNA]

Hi David H, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@chavelooo33]

Thank you very much, I like you method. Simple and straight to the point.

[@EnterpriseDNA]

Glad you liked it! Please dont forget to subscribe to our channel :)

[@allabout1135]

This is really useful. Thank You!

[@joelvazquezmarin3519]

Another very helpful video, already suscribed

[@EnterpriseDNA]

Awesome, thank you!

[@vasilisa6128]

Thank you! This video was helpful!

[@EnterpriseDNA]

Hi Vasilisa Glushkova, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@vasilisa6128]

@@EnterpriseDNA I already did)

[@wexwexexort]

Thanks you very much.

[@EnterpriseDNA]

Hi Eredin, glad that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@thaismachado6600]

Now I got it! Thanks

[@EnterpriseDNA]

Hi Thais Machado, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@PythonWithJoe]

The best Love your work man

[@EnterpriseDNA]

Hi Python With Joe, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@Devloper56]

Thank you !!!!!!!!!

[@EnterpriseDNA]

Hi Kunjal, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

[@harveyspector8367]

Awesome!!

[@EnterpriseDNA]

Hi Harvey, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@pranavindi7152]

thank you

[@EnterpriseDNA]

Hi Pranav Indi, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@andreibudaes3966]

don't get it. wouldn't SUM([Order Quantity]*[Unit Price]) return the same as SUMX?

[@Randyminder]

If you tried this you would get an error because the SUM function does not generate a row context and you need a row context to access multiple fields like you are doing in your example. You can access a single column without a row context but not multiple columns.

[@johnabram4159]

So the SUMX = Product

[@annagora6409]

so sumx is like sumproduct in excel...

[@giri41]

great view please help below code not working Test_1 = var PriceperUnit_CY = DIVIDE([Sales_CY],[Quantity_CY],0) var PriceperUnit_PY = DIVIDE([Sales_PY],[Quantity_PY],0) var SalesVariance = [Sales_CY]-[Sales_PY] return SUMX(VALUES(ATC[material_style1]),if(DIVIDE([Sales_CY],[Quantity_CY],0)=0 || DIVIDE([Sales_PY],[Quantity_PY],0)=0,[Sales_CY]-[Sales_PY],1))

[@ek_setera]

This is amazing! Thank you!

[@EnterpriseDNA]

Hi @ek_setera, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming data skills and AI tutorials, and announcements. Cheers!