Working with AverageX and Summarize to create averages in DAX
Вставка
- Опубліковано 19 вер 2024
- In this video Mitchell takes a look at some of the challenges that exist when working with averages and then how you can use a combination of DAX functions to work around those challenges. With Summarize and AverageX you can easilY create the averages you want.
We also take a look at modifying the filter context to include the last 3 months of data with the CALCULATE function and DATESINPERIOD! Enjoy the video!
If you want to explore formal DAX training including live bootcamps and recorded class content, please check out pragmaticworks...
#MitchellPearson #MitchellSQL #PragmaticWorks
This is the best Virtual table demo I've used and got me over the hump. I've been fighting this problem by burdening my model with helper tables in power query. This approach with DAX will level me up to take advantage of measures and the correct row context. THANK YOU!
Best Dax video I have ever watched bar none! You are a born educator. Bravo.
Thank you Mitchell for such an awesome video. I have learnt a lot from your teachings at Pragmatic Works. Your explanations are very easy to understand. You can explain difficult topics very easily.
Thank you Hafiz, glad to help!
The author is a genius
Talk for 20 minutes about what you can say in 5 minutes. This requires talent))
You made me laugh out loud. I am pretty sure this is not a complement, but I still like it :)
Many thanks for this! You have a talent in explaining complex things so well!! Please keep them coming!
This is awesome! I've been looking at how to show month average by year for a bit and I can't understand how everything works together. You just explained everything clearly, not just the average but the use of multiple useful combinations that can be applied in complicated calculation. Thanks!!!
Thank you Mitchell! The best video on UA-cam regarding the function.
Thank you for another great video Mitchell. I am really fond of your teaching style, the way you lay it out all in a nice and simple way. It sure sticks to the brain. Looking forward to watching more of it.
Thanks Khardu, glad to help!
Love your style Mitchell!! Thank you so much for these videos & great explanations. Hungry for more :). Greetings from Czech Republic / Europe. Martina
This is great feedback Martina, thank you so much :)
The best when it comes to DAX, thanks Mitchell.
Thanks for this, it helped me reason through a similar challenge I was facing.
That's awesome, glad it helped.
Thank you so much for the explanation! This is the best video I´ve ever watch about DAX, thank you again!!
Thank you very much, you explain really easily and it's easy to follow you and put into practice. I cannot thank you enough!!!
You're a life saver.. this is exactly what I needed for BI report.. thank you very much Kind Master 😊😊😊
Looking forward to see future video regarding SUMMARIZECOLUMNS vs SUMMARIZE in DAX.
Thank you for the step by step explanation. Really helpful to understand the logic behind the solution, many thanks.
Your explanations are always on point.
22' very well spent. Among other things, I appreciated the note on SUMMARIZECOLUMNS interaction of the context. Thanks Mitchell, I just subscribed your channel
Helped me a lot. Thanks for the video!
I'm follow you in pragmatic work !!! can't wait too subscribed this Chanel # love from Ethiopia
Such an amazing explanation!! Very very detailed! Loved it!!🎉
This was a great tutorial! You really took time to go through every line of code & explain. Would you ever consider doing an average by week? Again thank you for posting this :)
very well explained! I like the way you split the DAX formula into sections
Thank you Pawel, I'm glad you enjoyed it and found it helpful!
Thanks for your great teaching
Great video and a really good teaching style, thanks!
it would be great to get the files that you are using within your videos. according to me you are the one of the best in terms of teaching the background of the functions in daX
This was exactly what I needed, you’re great Mitchell!
great explanations ... thanks for your efforts to help us, to guide us, to teach us :)
Thank you so much. I was struggling with the same problem and you solve it.
Great breakdown, thanks so much! Easy to understand. Thanks again!
Amazing and fantastic explanation!
Well done. Look forward to your other videos.
Thank you! Glad you enjoyed the video.
great explanation, really loved this:)
I know I am 2 years behind 😂😂😂. But I want to see everything you touch, because everything you touch turns to golden knowledge. 😂😂 maybe co-pilot vs. Dax video
great tutorial. very useful... thanks.
Thanks Man. You made me understand filter and Row context!
Hey Mwangi, thanks for the feedback. I'm glad the video helped!
Excellent teaching style and awesome video.
Glad you liked it! Thanks!
Great video and thanks for taking the time to explain everything in detail. I know this video is a couple years old. Is there a way to not return a 3 month average for the first 2 rows where there is not enough previous data to give 3 month average?
Excellent lesson Mitchell...not sure if you have gotten yet to the promised video on the difference between "summarize" and "summarizecolumns"
This tips is really well
Awesome video sir Mitchell, and I think if you do others videos about DAX where you use virtual Tables inside formulas it will be great because in DAX to take further steps after understanding evaluation context
is to know how to create measure with using virtual tables.
after all it's great what you do sir! thanks
Thanks for watching and I'm glad you found this video helpful!
Thank you for the video! If there were no sales in some month, then how to calculate the average value correctly?
One thing to note, that if in the expression for AVERAGEX you use expression (e.g. SUM('Sales'[Price]) ) it will not give you result you expect.
It has to be a measure with exactly the same code (Total Sales = SUM('Sales'[Price]) ).
Very good observation! You are correct, X functions create a row context and since we are iterating over the date table we need to move that row filter into the filter context (Context Transition). Calling the calculated measure forces this transition to occur. We could write out the expression sum of sales amount but we would need to wrap it in a calculate statement like this: calculate(sum(salesamount)).
@@MitchellPearson thanks for clarification, I understand why that happens now.
Had quite a bit of WTF moments while trying to understand why my measure was not working and was always equal to just total SUM.
Great Sir. Thanx a lot
Thanks. What instead of total sales, we had to count the number of tickets per month and then average them per year pls?
Terrific video!!
You are the GOAT!!!
Hi, I congratulate you for the really interesting topic. I would like to ask you a question. How can I apply this formula create with DAX with Excel Power Query that uses M language? Tnx for your reply!
Its a gr8 video, why you are not making more videos
Good explanation , please share the practice dataset
how would this work if one of your months were blank? would the averageX remove the null before the summarize?
Hi, I know you mentioned values at the beginning, but is there any advantage to using Summarize?
I would just have used AVERAGEX( VALUES( Calendar [ MMM-YYYY] ) , [Total Sales] ) , I find Summarize a bit hard,
but have found it useful in reducing the rows of a table that need to be iterated over, so in a a total sales iterate over the sales table summarized by the dim product , customer etc.
How can we look at rolling averages over transactions instead of dates?
how to calculate monthly average include months with no record, in a matrix at Year and quarter levels
Can you make a video talking about the differences between SUMMARIZE() and GROUPBY()?
I would like to have a video clip about totalmtd, totalqtd and TOTALYTD. I always have a problem with totalmtd and totalqtd. It appears that it is related to the date table is end on the 31st Dec 2021 while I am working on the mtd for Aug... Wonder how to solve the problem
You rock sir👍
Excellent!
Hi @Mitchell, this is Raj from India. I need to achieve some scenario.
Let us assume 5 products were sold in the last month and 2 of them were sold in current month also along with
other products. Now, I need to exclude those 2 products and retrieve the count of last month sold products as 3 instead of 5.
eg: P1, P2, P3, P4 & P5 were sold in the last month. P1, P2, P6 & P7 were sold in current month.
Now I need to get the count of last month sold products but not this month as 3(only P3, P4 & P5 should be counted).
Please help me write the DAX measure code on this. Thanks in advance.
Great demo...thank you
Thanks for watching!
This was a great explanation 👏
Not sure if you guys gives a damn but if you guys are bored like me during the covid times you can watch all the new series on instaflixxer. Have been binge watching with my girlfriend for the last couple of months :)
@Allen Ray yea, been using InstaFlixxer for since december myself :)
Hey thanks for the explanation, how do the same with an id column instead of date as you did it with datesinperoid i want to have an average of the last 3 previous values using my id column that is unique in my table and is consecutive like an index column
'X' functions are like SUMPRODUCT in Excel, if I am not wrong to put it simply.
MItchell... great video... Qucik question, how would do you obtain the rolling average? Do you change the daysinperiod to -12months? Thanks
Great video! Thxs ; )
Hi, how to get the PBIX file for this video? Thanks.
Please come back here.... where are you
Excellent,Thanks
Thank you too!
Hi Thanks for your videos. I would like to ask a question if I may. I would be interested in how RLS works with SSAS live connection and import mode. Is there a chance you can address this topic in a video?
best regards and stay safe
Hi Mitchell, thanks for the nice Video, just was wondering how can we calculate the moving average for series of data in sequence of one minute or 5 minute, e.g. for a day have 1440 rows, appreciate your help.
Hi Mitchell - Is there old videos have been deleted?
Nice video as always.
I'm struggling with time and date functions. I'll email you a scenario if that's ok? What would be the best way to contact you
mpearson@pragmaticworks.com
Does not work for me.