Working with AverageX and Summarize to create averages in DAX

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

[@nikolaynikolayevich4469]

The author is a genius Talk for 20 minutes about what you can say in 5 minutes. This requires talent))

[@MitchellPearson]

You made me laugh out loud. I am pretty sure this is not a complement, but I still like it :)

[@nlopez2304]

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

[@Milhouse77BS]

Looking forward to see future video regarding SUMMARIZECOLUMNS vs SUMMARIZE in DAX.

[@zubair242]

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.

[@MitchellPearson]

Thank you Hafiz, glad to help!

[@Alma-im4qk]

Thank you so much. I was struggling with the same problem and you solve it.

[@damn68]

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

[@alano444]

Best Dax video I have ever watched bar none! You are a born educator. Bravo.

[@michelleliu1181]

Many thanks for this! You have a talent in explaining complex things so well!! Please keep them coming!

[@froggyana]

Thanks for this, it helped me reason through a similar challenge I was facing.

[@MitchellPearson]

That's awesome, glad it helped.

[@martasPD]

Love your style Mitchell!! Thank you so much for these videos & great explanations. Hungry for more :). Greetings from Czech Republic / Europe. Martina

[@MitchellPearson]

This is great feedback Martina, thank you so much :)

[@khardu]

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.

[@MitchellPearson]

Thanks Khardu, glad to help!

[@BrianStettenbenz]

Thank you Mitchell! The best video on KZbin regarding the function.

[@dan-is6mc]

Helped me a lot. Thanks for the video!

[@centugurdag7776]

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

[@cindypayet281]

Thank you very much, you explain really easily and it's easy to follow you and put into practice. I cannot thank you enough!!!

[@kavehyarohi2886]

The best when it comes to DAX, thanks Mitchell.

[@ceptebayi5385]

Thanks for your great teaching

[@patticmcconnell]

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

[@jahzielvaldes6948]

Thank you so much for the explanation! This is the best video I´ve ever watch about DAX, thank you again!!

[@randyblaschke1031]

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?

[@tangben9297]

You're a life saver.. this is exactly what I needed for BI report.. thank you very much Kind Master 😊😊😊

[@massimocolaceci]

22' very well spent. Among other things, I appreciated the note on SUMMARIZECOLUMNS interaction of the context. Thanks Mitchell, I just subscribed your channel

[@chakrabmonoj]

Excellent lesson Mitchell...not sure if you have gotten yet to the promised video on the difference between "summarize" and "summarizecolumns"

[@kostamadorsky]

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

[@MitchellPearson]

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

[@kostamadorsky]

@@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.

[@vasileiosmanasas4194]

Thank you for the step by step explanation. Really helpful to understand the logic behind the solution, many thanks.

[@jilalumustefa2276]

I'm follow you in pragmatic work !!! can't wait too subscribed this Chanel # love from Ethiopia

[@romanmagic9952]

Thank you for the video! If there were no sales in some month, then how to calculate the average value correctly?

[@worldofdata]

great explanation, really loved this:)

[@608er]

Your explanations are always on point.

[@krishj8011]

great tutorial. very useful... thanks.

[@mohityadav2162]

Such an amazing explanation!! Very very detailed! Loved it!!🎉

[@prokocim]

very well explained! I like the way you split the DAX formula into sections

[@MitchellPearson]

Thank you Pawel, I'm glad you enjoyed it and found it helpful!

[@jaggyjut]

Thanks. What instead of total sales, we had to count the number of tickets per month and then average them per year pls?

[@mimib9012]

how to calculate monthly average include months with no record, in a matrix at Year and quarter levels

[@shoaibrehman9988]

Its a gr8 video, why you are not making more videos

[@julliettecarignan8563]

This was exactly what I needed, you’re great Mitchell!

[@Pfreiker]

Great video and a really good teaching style, thanks!

[@hombreazu1]

Well done. Look forward to your other videos.

[@MitchellPearson]

Thank you! Glad you enjoyed the video.

[@edjabouvincent7369]

Amazing and fantastic explanation!

[@BbabbittGolf]

How can we look at rolling averages over transactions instead of dates?

[@johnabram4159]

'X' functions are like SUMPRODUCT in Excel, if I am not wrong to put it simply.

[@luigisir6893]

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!

[@ahmedharoon2740]

great explanations ... thanks for your efforts to help us, to guide us, to teach us :)

[@macemalakai]

Great breakdown, thanks so much! Easy to understand. Thanks again!

[@munidulam6212]

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.

[@andrewcoventry7246]

how would this work if one of your months were blank? would the averageX remove the null before the summarize?

[@tedbandaru]

Can you make a video talking about the differences between SUMMARIZE() and GROUPBY()?

[@prudhvireddy6452]

Good explanation , please share the practice dataset

[@azizquazi]

Hi, how to get the PBIX file for this video? Thanks.

[@DIGITAL_COOKING]

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

[@MitchellPearson]

Thanks for watching and I'm glad you found this video helpful!

[@garylbrown4]

Excellent teaching style and awesome video.

[@MitchellPearson]

Glad you liked it! Thanks!

[@VIJAY-pd4jt]

Great Sir. Thanx a lot

[@phamluu9079]

This tips is really well

[@mwangikaigi6152]

Thanks Man. You made me understand filter and Row context!

[@MitchellPearson]

Hey Mwangi, thanks for the feedback. I'm glad the video helped!

[@pepper_lab]

Please come back here.... where are you

[@DanielWeikert]

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

[@jazzista1967]

MItchell... great video... Qucik question, how would do you obtain the rolling average? Do you change the daysinperiod to -12months? Thanks

[@williamarthur4801]

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.

[@BbabbittGolf]

You are the GOAT!!!

[@arbaskar69]

Hi Mitchell - Is there old videos have been deleted?

[@williamstan1780]

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

[@angelnavarro503]

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

[@majiddini531]

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.

[@selenevera8834]

Great video! Thxs ; )

[@kennethstephani692]

Terrific video!!

[@fatihkokceoglu9438]

You rock sir👍

[@bhakthasingh8198]

Excellent!

[@sarris2412]

Great demo...thank you

[@MitchellPearson]

Thanks for watching!

[@techwithramsey]

This was a great explanation 👏

[@allenray9526]

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

[@rayangrayson7769]

@Allen Ray yea, been using InstaFlixxer for since december myself :)

[@mohammadrezaei4221]

Excellent,Thanks

[@MitchellPearson]

Thank you too!

[@zzzzzzzzzzzzzzzz9]

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

[@MitchellPearson]

[e-mail]

[@fuzzy889]

Does not work for me.