No video :(

DAX Fridays! #3: SUM and SUMX

Подписаться 135 тыс.

Просмотров 113 тыс.

50% 1

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/blog/glossary/SUM-dax
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/blog/glossary/sum-dax
curbal.com/blog/glossary/sumx...
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/donwload-center
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/contact
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

Опубликовано:

8 сен 2016

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 113

@VinayKumar-ij4eu 2 года назад

Can't find any other person who teaches so passionately as Ruth did!!! Lot's of respect for you!!

@CurbalEN 2 года назад

Thanks!

@praveenkumar_0143 Год назад

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!!👏👏👏

@CurbalEN Год назад

😃😃

@bethparks2602 4 года назад

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.

@LouG77 Год назад

I seriously think you have the best material out there. Thank you!!

@joanneosborne2428 4 года назад

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 RU-vid videos and website that keeps me from going completely insane. 😃❤👍

@CurbalEN 4 года назад

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

@Disaenz10 4 года назад

You are amazing, thank you for all the great material. I will do all your dax fridays videos 🙂

@jimmyni1983 7 лет назад

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.

@CurbalEN 7 лет назад

+Jimmy Excel Hi Jimmy! Thanks for the feedback and really happy you enjoy them! :) /Ruth

@TheDistractionStudio 7 лет назад

Thank You for DAX Friday's!!

@CurbalEN 7 лет назад

+rajan77 Thanks to you for watching!! :) /Ruth

@iliasvlachos3842 2 года назад

Better late than never - But DAX Fridays are really Top of your Teaching Work and Efforts !!!

@CurbalEN 2 года назад

Thanks!!

@KavyaTalks-Malayalam 4 года назад

Ruth, Your videos are really helpful!!. I just started from dax Fridays 1 today. Appreciate your effort making the videos!!!

@CurbalEN 4 года назад

Happy learning Kavya! /Ruth

@CTaLieN11 2 года назад

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

@CurbalEN 2 года назад

Welcome !! :)

@chamilam 5 лет назад

Thanks Ruth, Nice way to remember the difference.

@CurbalEN 5 лет назад

:) /Ruth

@Frank-iv6wd 4 года назад

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.

@CurbalEN 4 года назад

Thanks Frank! /Ruth

@vikramsingh-ws2nn 5 лет назад

Hi Ruth, This is Vikram from India Like these all videos SOOOOOOOOOOOOOOOOOOOOO much. Love you and god bless you.

@CurbalEN 5 лет назад

Thaaaaanks Vikram!! /Ruth

@RajivKumar-fj3on 7 лет назад

Thanks for detail explanation - great work - thanks for your time as well

@CurbalEN 7 лет назад

+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

@1989Bismillah 5 лет назад

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!

@CurbalEN 5 лет назад

Thanks! Really glad that they are helpful :) /Ruth

@gladiadordoexcel2358 4 года назад

I am from Guiné-Bissau, I love your vídeos on DAX. Thanks!

@CurbalEN 4 года назад

Hi 👋 and thanks! /Ruth

@francescotirinato856 4 года назад

You are amazing !! Please keep going on with this video format !! It's helping me a lot with DAX.

@CurbalEN 4 года назад

You have 160 videos more to check ;) Happy easter! /Ruth

@adventureboundbumpkins4483 4 года назад

Love your videos. Thank you so much.

@shoaibahmed1043 3 года назад

Your DAX Video very useful to us Thanks for making this course.

@mariabeatrizugalde4163 Год назад

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!!

@CurbalEN Год назад

Siiii, el primero de todos 😂😂

@edsonmaia3650 3 года назад

Hoje Ruth, I'm from Rio de Janeiro and I'm enjoying as well your hints. Tks.

@wendelsilva7055 7 лет назад

I really need this videos! Thanks for share.

@CurbalEN 7 лет назад

+Wendel Silva Many Thanks for watching! /Ruth

@AbhishekHanotiyaa 7 лет назад

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!!

@CurbalEN 7 лет назад

+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

@aliothsabik3383 5 лет назад

Thank you! Very clear explanation.

@CurbalEN 5 лет назад

Glad it was useful /Ruth

@jiyaadnaeem4802 5 лет назад

Thank you very much for these!

@CurbalEN 5 лет назад

You welcome! You have hundreds at your disposal ;) /Ruth

@shwanmohammed6390 7 лет назад

Thanks for your interesting tutorials!!!

@CurbalEN 7 лет назад

+Shwan Mohammed You welcome Shwan! /Ruth

@enochakabobby 4 года назад

This is great! Thx a lot!

@dhess5312 6 лет назад

Great video. Thank you,

@CurbalEN 6 лет назад

Going through all videos? :) /Ruth

@cristinatanase9313 Год назад

Great! It help a lot! ❤

@anandgp07 2 года назад

Thanks For the video

@davidgodri3982 6 лет назад

Great video!

@CurbalEN 6 лет назад

Thanks David :) Have a nice weekend! /Ruth

@SiddharthSharma21 5 лет назад

Thank you for these informative videos. Just one question, Why did you use (1- Discount)? Is it necessary to do that? Thanks

@RajivKumar-fj3on 7 лет назад

Thanks, keep the good work going

@CurbalEN 7 лет назад

+Rajiv Kumar Thanks to you Rajiv for watching! :) /Ruth

@iAbuZahrai 4 года назад

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.

@miltinhocamo3754 8 лет назад

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.

@CurbalEN 8 лет назад

+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

@areekorn1 5 лет назад

Nice and simple

@CurbalEN 5 лет назад

Great! That is how we like our DAX :) /Ruth

@jhimelic 4 года назад

can you use sumx if you are trying to do an expression that involves calling a column from another table?

@kari8070 3 года назад

Excellent explanation darling!

@CurbalEN 3 года назад

At your service :)

@kari8070 3 года назад

@@CurbalEN can u advise me which online course would be best for me to learn Power bi with DAx....,. Plz help me out..!

@CurbalEN 3 года назад

I don’t know, I am not up to date on what it is available :(

@kari8070 3 года назад

@@CurbalEN I would request u to guess at least......which will help a lot....! Plz curbal....come on....!!!!

@DarkOri3nt 5 лет назад

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 ?

@CurbalEN 5 лет назад

Hi Mark, The devil hides in the details, follow this steps to get help: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-oXFOebuvRz0.html /Ruth

@hasnainnourose804 5 лет назад

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

@CurbalEN 5 лет назад

Hi, Best way to get support is to contact the power bi community. /Ruth

@nav50 7 лет назад

hi, how can i sum or subtract values from differents tables? when each value is in a different table.

@CurbalEN 7 лет назад

+Nadav Knafo Hi Nadal, You can do like this: Total Sales:=SUM(Table1[Sales])+SUM(Table2[Sales]) /Ruth

@cooky123 5 лет назад

Loved it! btw I'm studying for the exam 70-778, do you have any tips?

@CurbalEN 5 лет назад

I haven’t taken it myself, so I am afraid I don’t. /Ruth

@ameyjawalikar872 5 лет назад

Hi Ruth, It is an interesting and good video. Keep posting Thanks.

@CurbalEN 5 лет назад

Thanks for the feedback Amey, /Ruth

@SmartBimson 7 лет назад

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 !!!!

@CurbalEN 7 лет назад

+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

@SmartBimson 7 лет назад

its Andy :-) and I figured it out, it was sumx together with relatedtable :-) Have a great evening Ruth !!! and thanks again ;-)

@CurbalEN 7 лет назад

+SmartBimson Hi Andy! Have a great evening too and nice to meet you! /Ruth

@chuckhamdan1283 4 года назад

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"]))

@HarshNathani 4 года назад

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 :)

@adi5187 7 лет назад

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]

@CurbalEN 7 лет назад

+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

@SHUBHAMSHARMA-tf2sv 4 года назад

hi , i am not able to download files

@maheshbabum986 5 лет назад

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?

@CurbalEN 5 лет назад

Maybe this will help you: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-SmXLgEHXSGc.html /Ruth

@maheshbabum986 5 лет назад

@@CurbalEN Thank you for the swift response Ruth, I got the answer for my queries. Thank you.

@CurbalEN 5 лет назад

Wonderful! Glad it helped :) /Ruth

@TobeEzeife 8 лет назад

I'm definitely onboard :D

@CurbalEN 8 лет назад

+Tobe Ezeife I am starting to feel the pressure!!! XD /Ruth

@maestrozaripov6750 2 года назад

it was incomprehensible part

@MahendraKumar-yc2hp 5 лет назад

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

@CurbalEN 5 лет назад

Hi, Can you please post in the power bi community instead? You will get help in no time! /Ruth

@MahendraKumar-yc2hp 5 лет назад

@@CurbalEN Hi, is there any specific link with u so that i can post.. thanks, Mahendra

@diannegillam8788 5 лет назад

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.

@HarshNathani 4 года назад

you can try lookupvalue()

@vlanra 6 лет назад

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.

@CurbalEN 6 лет назад

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