Excel Magic Trick 1398: DAX Formulas for Running Total and % of Running Total & other DAX Tricks

Download Files:

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

[LeilaGharani]

Great video on the power of DAX formulas! A lot of great info in the comments too :)

[LedermanM]

Just came across your videos...fantastic job! I love the way you explain each step of the DAX formulas - it really drives home how they work!

[deepakagrawal]

Just awesome!!! One of the best DAX videos ever, Mike. Many thanks for this crystal clear master piece. It cannot be more simpler than this. DAX at its best! 👌👌☺

[PowerBILearner-cd]

Hello. I don't understand this part: At 21:33 into the video, it is mentioned that the visual criteria flow into the MAX() function? Is it really so? Isn't MAX() working on the dRegularIncrements table after ALL() clears all visual filters? If MAX() is working on dRegularIncrements without the visual filters, how is running total calculated? At 22:44, the MAX() does pick the upper limit, but there is no ALL() function invoked before MAX() to remove the visual filters in this test/proof, which is why this test/proof succeeds. Please provide a more detailed explanation. Thank you!

[LotfyKozman]

I always sharpen my teeth when there is DAX video from your side.
This is more than amazing. Building the relation between the Quantity and Irregular frequency tables are really awesome, in addition to the power of the Values function. Thanks Mike :)

[ricvic]

Mike, can you explain how the year filter2 works, values just returns the single year but it seems the field isn't referenced as a comparitor, what am I missing?

[ExcelInstructor]

​ #ExcelIsFun Mr. Mike, do u have maybe a Power query video about Appending multiple files with different structures? let's say appending 10 files each with a different layout, but we need to append 3 columns from each file that contains the same data but sadly the column names are not the same and how we can do it automatically(periodically).

[andrewatherton]

What would be the effect of changing the ordering of the filters in the calculate cumulative total? Would it be more efficient to get the correct year before filtering down to the total over the months of that year?

[kristinkvingefinnesand]

You saved our exam!!! Thank You for this video

[benjygray]

Thank you. This is the most straight forward video on get an running total into a PivotTable. This really helped me create a YTD Sales total using a custom Fiscal Calendar.

[ljubicar]

You could also use folowing formula in (32:25) ---->
Quantity], DATESINPERIOD('dDate'[Date], EOMONTH(MAX('dDate'[Date]), 0), -12, MONTH))), VALUES('dDate'[Year]))

[drikusduminy]

Thanks for the great video's. Wish I discovered this a lot sooner. What is the benefit of using VALUES in the second Filter argument of CALCULATE?
Both return the same result.
CALCULATE([Total Quantity], FILTER(ALL(dDate),dDate[Date]<=MAX(dDate[Date])),VALUES(dDate[Year])) vs
CALCULATE([Total Quantity], FILTER(ALL(dDate),dDate[Date]<=MAX(dDate[Date])),dDate[Year])

[MySpreadsheetLab]

Amazing DAX playlist!! Thanks for the rad DAX Mike! ...time for more coffee 🍵

[danimr]

Learning in each every video thanks to you and your home keep safe:)

[kirstinlarson]

Thank you so much for your EXCELLENT videos! Your enthusiasm for the subject matter, and your clear explanations, are always so helpful! Some of the best excel-focused content anywhere!!

[MalinaC]

I was looking for running totals and... boom! ExcelIsFun on the first page :) Thank you, Mike :)

[numbadruma]

I was beating my head against the wall. but once I had a measure that was counting submissions, i could do the normal % running total in excel

[rimthorn]

Great video! Is there a possibility/video of showing running total or % of GT as bottom rows? I have a delivery plan of items (within product groups) with items as rows and weeks as colums and the quantities as values. At the bottom I want to show the totals per week on the first row. on the second bottom row the running total. On the third bottom row the % of the GT. And on the 4th bottom row the running % of the GT.

[mikedbman]

If one of the intervals has no results, then I see the Running total as blank. Is there a way to get the running total to carryforward from the previous interval instead of also being blank? (hope this makes sense)...

[atiqkhan]

Hi Mike i found one thing interesting that when i take dates from the fact table the formula for the cumulative total for quantity did not work, i was scratching for an hour then i changed the dates to the dimension table and it did work.
so what is the logic behind?