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




About this tutorial:

Video duration: 00:41:14
Download File:
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…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

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

    Shehan Thivanka

    (June 2, 2020 - 12:36 pm)

    I believe it is never too late to learn a cool trick like this! Thank you very much Mike!

    Atiq Khan

    (June 2, 2020 - 12:36 pm)

    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?

    Atiq Khan

    (June 2, 2020 - 12:36 pm)

    thats awesome, i am just getting on line, i started from zero with you.
    thanks

    TP

    (June 2, 2020 - 12:36 pm)

    Really cool video on DAX formulas to generate the Running Totals! This is one of those videos where I am glad there is a rewind button.

    Nitin Verma

    (June 2, 2020 - 12:36 pm)

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

    Armond Nazarian

    (June 2, 2020 - 12:36 pm)

    Amazing tricks!

    Dineth Prabash

    (June 2, 2020 - 12:36 pm)

    detailed. explained well. thanks

    Joad Lee

    (June 2, 2020 - 12:36 pm)

    Mike, I appreciate your inclusion of an irregular example to challenge us!

    kaas347

    (June 2, 2020 - 12:36 pm)

    I watched this video a while ago, but for some reason the main idea didn't click, and no one has ever really explained it this way, but it's one of the most important concepts I've learned in DAX. When you are doing Running Totals, the Field by which you are indexing, and need to reset the Calculation for distinct Running Totals, needs to have the VALUES function as one of the FILTER arguments of the CALCULATE function with the Field Name as its argument. Here, he is using VALUES([YEAR]) to have the Measure reset in the Pivot Table when the next year is reached. For my purposes, I am comparing oil and gas cumulative production numbers from a normalized time-zero. So I incorporate a calculated column for age to index each series with respect to time, and I use VALUES('Daily Production'[WELL]) to calculate a distinct Running Total for each individual well. The concept is the most important thing. I have only seen this kind of utilization of the VALUES function for Running Totals in this video. I hope you can do another video with a more general application and explanation to show its power for creating Running Total Measures with respect to chosen Fields at will.

    Benjy Gray

    (June 2, 2020 - 12:36 pm)

    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.

    Youshy RHA

    (June 2, 2020 - 12:36 pm)

    I really think your series are the best on explaining excel.. I have been going around in circles for the past 2 days figuring DAX formula for some occupancy dashboard.. and your video saved few more days of me searching… Thank you very much.

    Mike Hirsch

    (June 2, 2020 - 12:36 pm)

    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)…

    Steven Nye

    (June 2, 2020 - 12:36 pm)

    very well done

    Kirstin Larson

    (June 2, 2020 - 12:36 pm)

    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!!

    HIMANSHU DALAI

    (June 2, 2020 - 12:36 pm)

    Thank you Mike for this great video. Excellent DAX learning.

    Kevin Lehrbass

    (June 2, 2020 - 12:36 pm)

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

    Syed Hassan

    (June 2, 2020 - 12:36 pm)

    Is there any F9 key in power pivot to evaluate formula parts (as in MS Excel), please guide on this, as for DAX it is difficult to evaluate parts of the formula until and unless drop the measures/calculations into the excel. Kindly advise on this.

    Malina C.

    (June 2, 2020 - 12:36 pm)

    I was looking for running totals and… boom! ExcelIsFun on the first page 🙂 Thank you, Mike 🙂

    M Ismail

    (June 2, 2020 - 12:36 pm)

    hi sir
    i am a satore incharge
    i need running total for for different itms in single colomn
    please tell me

    Matt L

    (June 2, 2020 - 12:36 pm)

    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!

    kushagra deep

    (June 2, 2020 - 12:36 pm)

    Thank you for this great video…

    RRR program

    (June 2, 2020 - 12:36 pm)

    Hi Mike, Hope you know that I keep following all ur excellent tutorials… Any doubts I come back to video and work again …

    I am stuck with the below formula …. just little modification to what you have written.

    Running Total Mike:=CALCULATE([Frequency for Quantity],
    FILTER(ALL(dRegularIncrements), dRegularIncrements[Upper] <= MAX(dRegularIncrements[Upper])))

    The above works fine as shown in the video…. But if if modify a bit as below..

    Running Total 2:=CALCULATE([Frequency for Quantity],
    FILTER(ALL(dRegularIncrements[Upper]), dRegularIncrements[Upper] <= MAX(dRegularIncrements[Upper])))

    Instead of dRegularIncrements table…I specifically tried to filter out [Upper] column… and this isn't workingg :'(((
    Butttttt interestingly , if i follow the same 2nd failed logic for Total quantity as below

    Running Total Quantity:=CALCULATE([Total Quantity],
    FILTER(ALL(dDate[Date]), dDate[Date] <= MAX(dDate[Date])))

    This works and gives cumulative quantity….
    Can you please help me, Am i missing somethingg

    Isaac Mashiah

    (June 2, 2020 - 12:36 pm)

    at 21:58: the sum function will not obey row context, so it actually would not work

    René Imthorn

    (June 2, 2020 - 12:36 pm)

    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.

    Jiechao Chen

    (June 2, 2020 - 12:36 pm)

    Great video!…which brings to another question of how to use DAX to create difference between rows for non-date format, multi-dimensional tables. The non-DAX way of doing that I can think of is by duplicating the metrics table and use each of the column of a current.week – prev.week match table to join metrics tables, which is definitely not as efficient as DAX. Any suggestions or videos I can refer to? Thx

    Lotfy Kozman

    (June 2, 2020 - 12:36 pm)

    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 🙂

    Victor Friesen

    (June 2, 2020 - 12:36 pm)

    Great Video !!! I like the idea to combine ALL and VALUES in order to set up the right filters in CALCULATE. By now I was using ALLEXCEPT, but your solution is much faster, at least in some cases. Thumbs Up Mike !!

    Byron Molitar

    (June 2, 2020 - 12:36 pm)

    ONe more…you use the COUNTROWS function in DAX. However, you could have put Count of Quantity in the Value Field to get the same answer. Is the DAX formula faster, especially if we have lots of data? Thank you!

    Byron Molitar

    (June 2, 2020 - 12:36 pm)

    Love your stuff! At about 11:00, you comment that normally, you would pull the table down to encompass all the data (my words). Is there a way to expand and/or contract a table just using the number in cell W2, in your example? So, by putting 25 in W2, it would go down 20 rows instead of having to do it manually? A new Excel Magic Trick, perhaps? Thank you!

    robert cline

    (June 2, 2020 - 12:36 pm)

    When I try to add the DAX formula =CEILING(fQuantity[Quantity],50) Video position 8:15/41:13, my workbook freezes. I thought the workbook might be corrupted, so I recreated it. No change. The workbook freezes. Using 2016 Excel 365 ProPlus.

    How might I resolve this issue?

    Thank you.

    Vida

    (June 2, 2020 - 12:36 pm)

    Thanks for DAX fun. It seems some things (like Running Total) are simpler in regular Excel Pivot Tables. In my opinion, Microsoft should think about simplicity and consistency in their products.

    Robert Gruppetta

    (June 2, 2020 - 12:36 pm)

    Hi, is there a particular sequence (especially regarding playlists) that you would recommend following? or are all playlists able to take you from beg to end

    Leila Gharani

    (June 2, 2020 - 12:36 pm)

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

    lisa t

    (June 2, 2020 - 12:36 pm)

    Thanks Mike !! you explain very well !

    Miltinho Camo

    (June 2, 2020 - 12:36 pm)

    Amazing Mike… Thanks a lot.

    إبراهيم خطاب

    (June 2, 2020 - 12:36 pm)

    Awesome as usual. But don't you think, for the Year's running total, you could've just used one of the Date functions? Maybe DATESYTD, DATESBETWEEN or DATESINPERIOD?

    Best,
    Ibrahim Omar

    Arek Bombka

    (June 2, 2020 - 12:36 pm)

    Great video!!! Somehow couldn`t built the relationship from diregularIncrements Table to the diregularIncrements Column in fQuantity in the datamodel of PowerBI. Any Idea why?

    Muhammad Fathi

    (June 2, 2020 - 12:36 pm)

    Thanks for the stunning video!!!

    Deepak Agrawal

    (June 2, 2020 - 12:36 pm)

    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! 👌👌☺

Leave a Reply

Your email address will not be published. Required fields are marked *