Excel Magic Trick 1424: Average, Moving Average at Different Granularity: DAX or Excel Formulas?

Download File:
Start File: excelisfun.net...
Finished File: excelisfun.net...
Entire page with all Excel Files for All Videos: excelisfun.net
See how to calculate Average, Moving Average at Different Granularity in both DAX and Excel Formulas:
1. (00:14) Introduction
2. (00:27) What is Granularity?
3. (02:15) Look at Data Model and Hide Columns from Client Tool
4. (03:29) Basic DAX Calculation: Total Profit and Average Profit using the SUM Function and the AVERAGE Function
5. (04:39) Start PivotTable with DAX Transactional Average for each month. Granularity: Transactional.
6. (05:22) Excel Spreadsheet formula to calculate Transactional Average for each month using AVERAGEIFS function. Granularity: Transactional.
7. (06:52) Discuss Filter Context and how it works in the Power Pivot Data Model
8. (07:41) DAX Formula for Transactional Moving Ave Profit Last 90 Days using CALCULATE and DATESINPERIOD. Granularity: Transactional.
9. (10:29) Excel Spreadsheet formula to calculate Transactional Moving Ave Profit Last 90 Days using AVERAGEIFS function. Granularity: Transactional.
10. (11:55) Create Line Chart to plot Month Average and 90 Day Moving Average
11. (13:08) DAX Formula to calculate Daily Average Profit. Granularity: Daily. See the AVERAGEX Function and the Measure for Total Profit perform a calculation due to Filter Context and Row Context. Discussion of Context Transition with CALCULATE Function converting Row Context into Filter Context.
12. (16:38) Excel Array Formula to calculate Daily Average Profit. Granularity: Daily. See the functions: AVERAGE, SUMIFS, ROW and INDIRECT. See how to generate (materialize) an entire table of valid dates in a cell formula using an Array Formula. Learn how SUMIFS can add with and OR Logical Test to generate an entire list of Daily Sales in a single cell formula.
13. (22:59) DAX Formula to calculate Daily Moving Ave Profit Last 90 Days. Granularity: Daily. See the Function DATESINPERIOD to generate a va;id list of dates as a table for the AVERAGEX Function.
14. (25:18) Excel Array Formula to calculate Daily Moving Ave Profit Last 90 Days. Granularity: Daily. See the functions: AVERAGE, SUMIFS, ROW and INDIRECT.
15. (26:38) Summary

[@deepakagrawal465]

Excellent video, Mike. DAX was never so easy but with your videos it's a different story altogether. Thanks a ton!!!

[@excelisfun]

You are welcome a ton! Thank you for your kind words!! : ) : ) A good story usually makes a difficult topic more easily understood : )

[@LotfyKozman]

Awesome Video, you started to make life pretty easy with DAX. Thanks Mike

[@excelisfun]

You are welcome! DAX is real fun : )

[@vida1719]

Amazing video! Learned not only new DAX functions (DATESINPERIOD very useful), but also Excel’s ones. Especially enjoyed ROW&INDIRECT together. Thanks a lot.

[@excelisfun]

You are welcome a lot : )

[@williamarthur4801]

Really intrigued by the use of INDIRECT, only used it in usual way ( INDIRECT("Sheet1!"&"A1",etc.), never seen it used in that way, been experimenting, still not sure how it works. Surprised not to have come across this one before, paranoid about granularity, so easy to get wrong. Great work. Thanks.

[@shoeshines2121]

Another epic video here! I definitely enjoyed the part where ROW(INDIRECT) formula element materialized the valid list of dates. Also, a great piece of information was that the "criteria" argument of any "IFS" function performs an OR logical test when an array of values is used. DAX on the other had makes life very easy once the evaluation contexts are understood.

[@user-li7mq5hp9g]

Excel is really fun with you!

[@excelisfun]

Yes!!!!

[@zaighamuddinfarooqui1705]

Awesome, Amazing, Excellent etc. etc. etc.

[@excelisfun]

Thank you very much! : ) This one was an especially fun one to make.

[@automationguide3498]

Thank You. Lot of fun watching Row and InDirect

[@hosseinhosseinpoor9561]

خیلی عالی

[@PEDRUSCOT]

Great!!!

[@excelisfun]

Glad it is great for you, Pedro : )

[@drsteele4749]

That ROW(INDIRECT...) bit is a winner! Terrific stuff all 'round. I wonder though, how can that DAX measure be altered to include that 90 from the worksheet instead of having it hardcoded into the DAX model? It'd be handy to be able to just change it in the worksheet to 60 or 120, etc.

[@excelisfun]

Glad you like the ROW INDIRECT! Have you read my book about Array Formulas? That is one of many important Array Formula Elements that can be used in many different Array Formulas. I have done videos about how to use values from Excel in DAX. For this example you would convert the 90 to a single column, single item table, import it into the Data Model and then use VALUES Function to access the column with the single value.

[@excelisfun]

Wait... It doesn't look like you click Thumbs Up... Did you not click Thumbs Up?

[@drsteele4749]

You're an auto-thumbs-up, Mike! And I will buy your book. Excel rocks!

[@excelisfun]

It didn't look auto...

[@ExcelInstructor]

correct me if I'm wrong Mr. Mike, but this sounds overly complicated to me. generally an average formula for X days is the sum of that period divided by X. So average for 5 days would be the sum from 5 days divided by 5. so why not do the sumifs() function with the upper / lower limit as the date and then divided it by day() function from the upper limit ?

[@vida1719]

I just was playing with your data on Power BI (I don’t have Power Pivot) and noticed that if company is not trading on e.g. weekends, Power BI would calculate correct Daily Average Profit, while Excel formula needs to be changed. I used this one: =SUMPRODUCT(SUMIFS(fUnits[Profit],fUnits[Date],ROW(INDIRECT(J60&":"&K60))))/NETWORKDAYS(J60,K60) I have not included holidays such as Christmas

[@excelisfun]

Very nice, Vida : )

[@Al-Ahdal]

Wow........... great video. Could you advise, how to improve in DAX? Do you have any book on that?

[@excelisfun]

DAX is just flat out hard to learn. The best books that I learned from are: MS Excel 2013 Building Data Models in PowerPivot The Definitive Guide To DAX DAX Patterns all three are authored by Macro Russo and Alberto Ferrari

[@Al-Ahdal]

ExcelisFun is the best channel under the leadership of Mike. Tons of thanks for this.

[@excelisfun]

Tons of "You are welcomes!!!"

[@JessPedersenCPH]

Excellent information and truly useful in understanding the aggregation / granularity sum calculations. I have a question in this regard - when using the formula AVERAGEX(dDate,[Total Profit]) it will of course average based on the total profit grouped by the dDate aggregation - in this case dates. If I do not have a value in my transaction table for a specific date my average would be higher compared to if I would assume a 0 on a date with no transactions. Example 1: (8,34) Monday 10 Tuesday 15 Wednesday 0 Example 2: (12,5) Monday 10 Tuesday 15 Wednesday (null) How do I calculate the average based on example 1? (and thereby assuming a date has a Zero if no transactions exists for this date) Again - thank you for these excellent videos. Highly appreciated.

[@joad_lee]

See kzbin.info/www/bejne/n2LKi3mVer6risU

[@excelisfun]

: )

[@valerieolivier1942]

Hello Mike, Thanks a lot for all your video that I look from France. I would like to know which simply measure you use to calculate the average if some row have no amount.

[@joad_lee]

See kzbin.info/www/bejne/n2LKi3mVer6risU

[@excelisfun]

: )

[@Al-Ahdal]

Mike, is there any method to hide DAX measures formula (whether implicit or explicit) in power bi or power query or do something in this matter. Kindly suggest.

[@excelisfun]

I am not sure what you mean by hide.

[@Al-Ahdal]

@@excelisfun, hiding explicit measure Formulas, so no other than me can able to see that. Is it possible in power query or power bi.

[@excelisfun]

@@Al-Ahdal Sure. Right-click Measure and then click on Hide From Client Tool (in Excel) and Hide in Report View (in Power BI Desktop)

[@Al-Ahdal]

@@excelisfun Thank you boss.

[@AnujSharma-wt8bw]

Hello mike. my name is Anuj sharma , I'm from India. I know nothing about excel can u plz suggest me from which video should i start (as in from basic to advance type). thank u

[@excelisfun]

Here is the Excel Basics video series: kzbin.info/aero/PL3FBEE51974F03CCF Thank you for your support with a Sub and Thumbs Up on each video that you watch.

[@AnujSharma-wt8bw]

thank u :)