Download Files:

Start File: https://excelisfun.net/files/EMT1398Start.xlsx

Finish File: https://excelisfun.net/files/EMT1398Finished.xlsx

See how to:
1. (00:15) Introduction
2. (02:45) Look at Data Model in Download file. This is the Data Model that we start with at beginning of video.
3. (04:55) Add Upper & Lower Limit “Regular Increment” Category Table to Data Model.
4. (05:01) 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.
5. (08:06) Build Relationship between Fact Table and Regular Increment Table.
6. (08:42) 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.
7. (10:43) 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.
8. (13:16) 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!!!!!!!!!!
9. (14:44) Frequency DAX Formula using COUNTROWS Function.
10. (17:15) Create a Running Total (Cumulative Frequency) formula using the DAX Functions: CALCULATE, FILTER, ALL and MAX. This is a “Count” Running Total.
11. (23:55) Create a % Running Total, % Cumulative Frequency using the DAX Functions: DIVIDE, CALCULATE and ALL.
12. (26:10) Create DAX formula for SUM of Monthly Totals
13. (28:13) 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.
14. (32:55) Create a % Running Total (% Cumulative Total) across Months using the DAX Functions: DIVIDE, CALCULATE and ALLEXCEPT.
15. (34:57) Import 2nd Upper & Lower Limit “Irregular Increment” Category Table to Data Model.
16. (35:28) 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.
17. (39:09) Build Relationship between Fact Table and Regular Increment Table. Then build a PivotTable based on the irregular incremented categories with Lower and Upper Limits.
18. (40:00) 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.
19. (40:42) Summary

Reference Videos:
Excel Magic Trick 1396: PivotTable Show Values As: Running Total and % of Running Total (2 Examples)
Excel Magic Trick 1397: Excel Count Formulas for Running Total and % of Running Total & Ogive Chart
Excel Magic Trick 1397 Part 2: Formulas: Monthly Revenue, Running Total & % Running Total & Chart
Excel Magic Trick 1397 Part 3: % Frequency Running Total Formula w Irregular Increments & X-Y Scatter