DAX Formulas for Running Total and % of Running Total & Other DAX Tricks

Overview

Dive into advanced Excel techniques with this comprehensive 41-minute tutorial on DAX formulas for running totals and percentage calculations. Learn to create calculated columns, build relationships between tables, and utilize DAX functions like CALCULATE, FILTER, ALL, and MAX for both regular and irregular increments. Master the art of sorting categories, creating frequency formulas, and generating cumulative totals across months and years. Explore how to import and manipulate data models, use approximate match lookups, and construct PivotTables for effective data analysis. Perfect for Excel enthusiasts looking to enhance their data modeling and formula-writing skills.

Syllabus

) Introduction.
) Look at Data Model in Download file. This is the Data Model that we start with at beginning of video..
) Add Upper & Lower Limit “Regular Increment” Category Table to Data Model..
) Create Calculated Column for a Relationship between Regular Increment table and Fact table when the increments for the counting categories are consistent using the CEILING DAX function..
) Build Relationship between Fact Table and Regular Increment Table..
) Use VALUES DAX Function to connect CEILING significance amount to an Excel Table. This allows the increment in the DAX Formula to be connected to a cell in the Excel sheet..
) Sort Counting Category (Upper and Lower Limit) Column by the Upper Limit column so the text column will sort correctly in the Row Area of the PivotTable..
) Discussion of DAX Formulas, Data Model and Filtered Tables and how they interact when the formula is calculated (evaluated). THIS IS THE MOST FUN PART OF THE VIDEO!!!!!!!!!!.
) Frequency DAX Formula using COUNTROWS Function..
) Create a Running Total (Cumulative Frequency) formula using the DAX Functions: CALCULATE, FILTER, ALL and MAX. This is a “Count” Running Total..
) Create a % Running Total, % Cumulative Frequency using the DAX Functions: DIVIDE, CALCULATE and ALL..
) Create DAX formula for SUM of Monthly Totals .
) Create Running Total (Cumulative Total) for adding the quantities across Months and Years using the DAX Function: CALCULATE, FILTER, ALL, MAX and VALUES. This is a “SUM” Running Total..
) Create a % Running Total (% Cumulative Total) across Months using the DAX Functions: DIVIDE, CALCULATE and ALLEXCEPT..
) Import 2nd Upper & Lower Limit “Irregular Increment” Category Table to Data Model..
) Create Calculated Column for a Relationship between Irregular Category Table table and Fact table when the increments for the counting categories are irregular using the DAX Functions: CALCULATE, MAX and the FILTER. See how to do Approximate Match Lookup in a DAX Formula..
) Build Relationship between Fact Table and Regular Increment Table. Then build a PivotTable based on the irregular incremented categories with Lower and Upper Limits..
) For Irregular Incremented Table, Sort Counting Category (Upper and Lower Limit) Column by the Upper Limit column so the text column will sort correctly in the Row Area of the PivotTable..
) Summary.