DAX Fridays! #3: SUM and SUMX
ฝัง
- เผยแพร่เมื่อ 5 ต.ค. 2024
- Dax Fridays! Today we will go through: how to use SUM() and SUMX(), the difference between them and when should we use one vs the other.
The DAX SUM function calculates the sum of all numbers in a column while the DAX SUMX function returns the sum of an expression for each row in a table.
02:28 Create a measure to calculate the total sales using SUMX function
03:40 Create a calculated column for total sales (row by row)
05:16 Create a measure to calculate the total sales using SUM function
06:00 Difference between SUM and SUMX
Link to Curbal Glossary: curbal.com/blo...
PREVIOUS VIDEO: • DAX Fridays! #2: CALEN...
NEXT VIDEO: • DAX Fridays! #4: COUNT...
For this you need the Northwind dataset: • Northwind data source:...
or you can download our example HERE: curbal.com/blo...
curbal.com/blo...
Keynotes:
Northwind datasource 01:44
SUMX 02:39
SUM 04:45
Calculated column vs measure 06:44
Looking for a download file? Go to our Download Center: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
Join our DAX Fridays! Series: goo.gl/FtUWUX
Power BI dashboards for beginners: goo.gl/9YzyDP
Power BI Tips & Tricks: goo.gl/H6kUbP
Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
► Linkedin: goo.gl/3VW6Ky
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP
Can't find any other person who teaches so passionately as Ruth did!!! Lot's of respect for you!!
Thanks!
Hi Ruth, I am relatively new to DAX and Power BI and recently found your playlist for DAX. It is helping me immensely! I can't thank you enough for all the time you have invested in this series and I can only imagine how many other's this has helped. I look forward to going through all of your videos.
DAX Fridays!! is the most valuable content available for everyone who is learning dax for the first time and appreciate your efforts in making these videos Ruth!!👏👏👏
😃😃
Hi Ruth, I am from Brazil, São Paulo and I am loving your short videos, but very clear ones. Thanks a lot and please please keep up your this great job. Hugs to you.
+Jimmy Excel Hi Jimmy! Thanks for the feedback and really happy you enjoy them! :)
/Ruth
Thank you so much, Ruth, You Rock!! I'm following along and it's really helpful to have all these videos to guide me. Amazing. :>) It feels like you are sitting beside me helping each step of the way. Plus you are so down to earth and I love that about you. I will follow all of these videos that you created and then I will go thru them again and again. I'm just a newbie and it's your TH-cam videos and website that keeps me from going completely insane. 😃❤👍
Thanks a million for taking the time to give me this amazing feedback, it really helps me to keep motivated and continue the work :)
Have a great weekend!!
/Ruth
I've just started learning DAX and am grateful to have come across your series- there's not another series like it, clear and comprehensive, thank you!
Thanks! Really glad that they are helpful :)
/Ruth
Hi Ruth, This is Vikram from India Like these all videos SOOOOOOOOOOOOOOOOOOOOO much. Love you and god bless you.
Thaaaaanks Vikram!!
/Ruth
I seriously think you have the best material out there. Thank you!!
Better late than never - But DAX Fridays are really Top of your Teaching Work and Efforts !!!
Thanks!!
I've just started this series and feeling its gonna be so much helpful. Thank you very kindly for all your efforts in helping us come out of the darkness of not knowing :D
Welcome !! :)
OMG Is this the first DAX Friday video?? Thank you so much for your work and dedication, yo have helped me very much.
Saludos desde Chile, espero poder probar pronto el Queso Cabrales!!
Siiii, el primero de todos 😂😂
Hi Ruth,
I was waiting for a long time to start on your DAX Friday series, but I finally did it today, sadly its not Friday :( , This is really good, and thanks for creating this playlist, it is really useful resource. Keep it going, please. Thanks!!
+Abhishek Hanotiya Hi!! Thanks! Really happy that you find it useful. I will be releasing very soon a New DAX resource that hopefully will get you going even faster. Stay tuned!
.. and it is only 2 days until Friday so I it is ok to start now :)
/Ruth
Hoje Ruth, I'm from Rio de Janeiro and I'm enjoying as well your hints. Tks.
Hi! Just came across your channel and it is informative and precise. You're brilliant. Thank you for sharing and I look forward to what I can learn from your existing and future contributions to the community.
Thanks Frank!
/Ruth
You are amazing, thank you for all the great material.
I will do all your dax fridays videos 🙂
Ruth, Your videos are really helpful!!. I just started from dax Fridays 1 today. Appreciate your effort making the videos!!!
Happy learning Kavya!
/Ruth
I am from Guiné-Bissau, I love your vídeos on DAX. Thanks!
Hi 👋 and thanks!
/Ruth
Your DAX Video very useful to us Thanks for making this course.
Thank You for DAX Friday's!!
+rajan77 Thanks to you for watching!! :)
/Ruth
Thanks Ruth, Nice way to remember the difference.
:)
/Ruth
You are amazing !! Please keep going on with this video format !! It's helping me a lot with DAX.
You have 160 videos more to check ;)
Happy easter!
/Ruth
Thanks for detail explanation - great work - thanks for your time as well
+Rajiv Kumar Hi Rajiv, and thanks to you for taking the time to give me feedback! I really appreciate it :)
Have a nice weekend,
/Ruth
Thank you for these informative videos. Just one question, Why did you use (1- Discount)? Is it necessary to do that? Thanks
Hi Ruth... Wonderful idea... In fact... I would prefer 2 o 3 Dax Days more.. ;d
I´ll wait your next video with great expectation.
Have a nice weekend.... Miltinho.
+Miltinho Camo (Miltiño) Hi Miltinho, Great!...,I am starting to feel the pressure already..:)
It's going to be fun and thanks for the feedback.
Have a great weekend you too /Ruth
I really need this videos! Thanks for share.
+Wendel Silva Many Thanks for watching!
/Ruth
Excellent explanation darling!
At your service :)
@@CurbalEN can u advise me which online course would be best for me to learn Power bi with DAx....,. Plz help me out..!
I don’t know, I am not up to date on what it is available :(
@@CurbalEN I would request u to guess at least......which will help a lot....! Plz curbal....come on....!!!!
Thanks For the video
Love your videos. Thank you so much.
Thanks for your interesting tutorials!!!
+Shwan Mohammed You welcome Shwan!
/Ruth
Great video. Thank you,
Going through all videos? :)
/Ruth
Great! It help a lot! ❤
Thank you! Very clear explanation.
Glad it was useful
/Ruth
Thanks, keep the good work going
+Rajiv Kumar Thanks to you Rajiv for watching! :)
/Ruth
This is great! Thx a lot!
You stated that a calculated column is needed to do other calculations in Power BI. That is not true - you can work from Measures to do this. Measures are just as accurate and faster.
Hi, this is one of the first Dax Fridays videos I did, I was and still are learning. Apologies if it was misleading to you.
/Ruth
Great video!
Thanks David :)
Have a nice weekend!
/Ruth
Thank you very much for these!
You welcome! You have hundreds at your disposal ;)
/Ruth
can you use sumx if you are trying to do an expression that involves calling a column from another table?
it was incomprehensible part
Hi Ruth,
Thanks for the video. How can you sum two values from two different tables? I have tried to use it as a measure but the thing is that it duplicates the rows.
Thanks and hope to hear from you.
Hey Ruth, I have a questions regarding the DAX but i don´t know if sumx is the right Expression for my case..
I have 2 tables:
tbl_1) Contract Number / Contract Sume / Project Name
tbl_2) Contract Number / Change Order Sum
There can be a lot of "Change Orders" during a Project, so ist a 1 to many relation.
What I want is either a measure or a calculated row where i have next to the tbl_1 Contract Sum also the SUM of all Chage Orders.
In Excel I would do it with vlookup... but in Power Bi and with dax I still cant figure out how to do it. I thought maybe sumx will help me.. but not so much yet.
thanks a lot already for your answer and have a great Weekend Ruth !!!!
+SmartBimson Hi, could you please ask the question in the power bi community? They will give you a better and faster answer!
Have a great weekend ... what is your name?
/Ruth
its Andy :-)
and I figured it out, it was sumx together with relatedtable :-)
Have a great evening Ruth !!! and thanks again ;-)
+SmartBimson Hi Andy! Have a great evening too and nice to meet you!
/Ruth
HI,
Your tutorials are nice, I have a query. Please help!
I have 2 columns (X and Y) with 10 rows each.
1. In X column top 4 rows are blank and rest 6 rows have values.
-----X Column Values: [Blank, Blank, Blank, Blank, 34,56,76,87,98,98]
2. In Y column top 2 rows are blank and rest of the raws have values.
-----Y Column Values: [Blank, Blank, 233, 333, 334, 456, 5765, 58, 5598, 6798]
Now as per the requirement, I need a calculated column Named "XYZ", this column needs to be populated by the count of values in each row of both columns X and Y
-----"XYZ" Column Values: [0, 0, 1, 1, 2, 2, 2, 2, 2, 2]
+Gaurav Chhabra Hi Gaurav,
This question is perfect for the Power BI Community. Create a post right away and they will be able to help you right away!
/Ruth
Nice and simple
Great! That is how we like our DAX :)
/Ruth
hi ruth Please make video on CAGR
how we can calculate CAGR in power bi
thanks
Sure!
/Ruth
@@CurbalEN Thank you Ruth 😊😊😊
Please guide me What is the difference between New measure and New Column? And when do we go for New measure and when do we go for New column?
Maybe this will help you:
m.th-cam.com/video/SmXLgEHXSGc/w-d-xo.html
/Ruth
@@CurbalEN Thank you for the swift response Ruth, I got the answer for my queries. Thank you.
Wonderful! Glad it helped :)
/Ruth
Hi Ruth, I have been watching your videos for a while and fine then very helpful. I have a question thou. I have 2 tables. Table 1 has customer names etc and table 2 has values etc. I have a relationship between the 2 tables based on the customer name. I want to be able to pick out the customers in Table 1 who have a value(s) in table 2. I have tried everything but I don't know what I am doing wrong. Please help.
you can try lookupvalue()
hi , i am not able to download files
Hi Ruth, It is an interesting and good video. Keep posting Thanks.
Thanks for the feedback Amey,
/Ruth
Hi All/Ruth,
I have got one requirement in power Bi report but i could not able to apply in my report.
Requirement:
I am calculating the no hours for each order no and splitting based on the date_period(12-01-2019) in pivot view in power Bi.
1)Current hours (coming from DB view)
2)available hours(i am populating 18 as default for all working days)
3)Late hours(i am grouping the current hours for first 10 days in date_period)
3)hours left (this is calculated item i want to do in the power Bi but facing issues in calculating)
formula for Hours left=previous day hours left+current hours(present day)-Available hours
initial/first value for hours left =Late hours+current hours(present day)-Available hours
Please help me in solving the above requirement.
Thanks in Advance!!
Mahendra
Hi,
Can you please post in the power bi community instead? You will get help in no time!
/Ruth
@@CurbalEN Hi,
is there any specific link with u so that i can post..
thanks,
Mahendra
Loved it! btw I'm studying for the exam 70-778, do you have any tips?
I haven’t taken it myself, so I am afraid I don’t.
/Ruth
Hi ruth, how do you do a sum on a min calculation. I have one measure with min(col) but I want to sum it on 2 levels group and subgroup in this case on the sub group level the calculation is correct but on the group level it gets a min of all the sub groups. What am I doing wrong ?
Hi Mark,
The devil hides in the details, follow this steps to get help:
m.th-cam.com/video/oXFOebuvRz0/w-d-xo.html
/Ruth
Hi Ruth, Do you have a sample where you would use the SUM with a condition to get the total for all the same product with the same product code?
What if you have let us say: calculate the number of students for a State for which each state has a certain number of agency and each agency has a count of number of student.
e.g. State Agency Total Student
AZ ABC 30
AZ BCD 15
FL EFG 10
FL HIJ 5
How would you do it? I tried the following DAX Formula but it is giving me an error.
CALCULATE (
DISTINCTCOUNT ( EnglishLanguageEnrollment[#"Total Students"]),
ALLEXCEPT (EnglishLanguageEnrollment, EnglishLanguageEnrollment[#"State Name"]))
A better representation of this can be done through the matrix visualization. You can get the all the sum as per State and Agency in a matrix visualisation.
To make it work with a Slicer.You can try the below function:
Total =
var totalstate = CALCULATE(SUM(Students[Total Students]),ALLEXCEPT(Students,Students[State]))
var totalagency = CALCULATE(SUM(Students[Total Students]),ALLEXCEPT(Students,Students[Agency]))
var stateinscope = ISINSCOPE(Students[State])
var agencyinscope = ISINSCOPE(Students[Agency])
var other = CALCULATE(SUM(Students[Total Students]),All(Students))
return
SWITCH(
true(),
agencyinscope,totalagency,
stateinscope,totalstate,
other
)
PS : I am just a beginner and have seen this in one of the Ruth's video and thought of giving your example a try :)
Thank You Ruth for making DAX easy to learn :)
what if i like to calculate total sales for each product id , actually i am looking to calculate standard deviation for each product id .
i am stuck somehow ,if you can kindly guide me
Thanks
Hi,
Best way to get support is to contact the power bi community.
/Ruth
hi,
how can i sum or subtract values from differents tables? when each value is in a different table.
+Nadav Knafo
Hi Nadal,
You can do like this: Total Sales:=SUM(Table1[Sales])+SUM(Table2[Sales])
/Ruth
good madam
I'm definitely onboard :D
+Tobe Ezeife I am starting to feel the pressure!!! XD
/Ruth
Why is there so much non-interesting brabble in the first two minutes of the video?
It gets better in the newer videos !
U r good but slow
It gets better on newer videos ;)
/Ruth