Average, Moving Average at Different Granularity - DAX or Excel Formulas

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Learn how to calculate average and moving average at different granularity levels using both DAX and Excel formulas in this comprehensive 28-minute tutorial. Explore concepts like transactional and daily granularity, filter context, and context transition. Master DAX functions such as SUM, AVERAGE, CALCULATE, DATESINPERIOD, and AVERAGEX, as well as Excel functions including AVERAGEIFS, SUMIFS, ROW, and INDIRECT. Create PivotTables, line charts, and array formulas to visualize and analyze data effectively. Gain practical skills in handling complex calculations and data manipulation techniques for improved financial analysis and reporting.

Syllabus

) Introduction.
) What is Granularity?.
) Look at Data Model and Hide Columns from Client Tool.
) Basic DAX Calculation: Total Profit and Average Profit using the SUM Function and the AVERAGE Function.
) Start PivotTable with DAX Transactional Average for each month. Granularity: Transactional..
) Excel Spreadsheet formula to calculate Transactional Average for each month using AVERAGEIFS function. Granularity: Transactional..
) Discuss Filter Context and how it works in the Power Pivot Data Model.
) DAX Formula for Transactional Moving Ave Profit Last 90 Days using CALCULATE and DATESINPERIOD. Granularity: Transactional..
) Excel Spreadsheet formula to calculate Transactional Moving Ave Profit Last 90 Days using AVERAGEIFS function. Granularity: Transactional..
) Create Line Chart to plot Month Average and 90 Day Moving Average.
) 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..
) 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..
) 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..
) Excel Array Formula to calculate Daily Moving Ave Profit Last 90 Days. Granularity: Daily. See the functions: AVERAGE, SUMIFS, ROW and INDIRECT..
) Summary.