***** 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
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.
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!
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.
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?
@@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.
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.
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 TH-cam 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/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
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!
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!
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
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!
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.
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!
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
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!
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.
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))
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!
![[UNCUT] The Loyal Pin ปิ่นภักดิ์ EP.15 (2/4)](http://i.ytimg.com/vi/qbHbshrA1f0/mqdefault.jpg)
***** 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
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.
Awesome awesome job. This has confused me for a long time, and here's the answer in 7 1/2 minutes!
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!
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.
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?
Exactly! Sorry, I wrote my comment before reading yours....
@@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.
Great job! What if you have to multiply two columns from
different tables
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.
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 TH-cam 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/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Great efforts to educate us carefully, thanks alot, Sam.
Hi Mohammed, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Awesome explanation
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!
This is awesome! thank you
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!
Many thanks. Very articulate explanation.
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Thank you very much, I like you method. Simple and straight to the point.
Glad you liked it! Please dont forget to subscribe to our channel :)
Hi sir.Where i can find these data sources.
Well Explained. Thanks
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!
Fantastic clarification
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.
Thank you !!!!!!!!!
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!
Thanks you very much.
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!
Made sense when you explained it!! Thank you!!
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Now I got it! Thanks
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
thank you
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Thank you! This video was helpful!
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
@@EnterpriseDNA I already did)
This is really useful. Thank You!
Another very helpful video, already suscribed
Awesome, thank you!
The best
Love your work man
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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Awesome!!
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!
don't get it. wouldn't SUM([Order Quantity]*[Unit Price]) return the same as SUMX?
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.
so sumx is like sumproduct in excel...
So the SUMX = Product
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))
This is amazing! Thank you!
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!