You’re the best I’ve came across btw. Explanations, definitions, professional terse intro with oblivious depth and passion is how I like it. Your dashboard training pretty much catapulted my career. My running totals stop at month ends, then rests every month. My lookup date column is week ending dates only. I wonder if that’s why. John Texas
Wow, that's amazing to hear, John! Not sure what's causing your RT to reset each month. You can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I’ve seen many masters in excel…but you are outstanding. You are doing with so confidently, we have to pause the video to understand. Curious to know how long you are using this excel ?
Thanks for your kind words 😊 I’ve been using Excel for nearly 30 years 😉 but really my skills improved significantly when I started teaching others about 12 years ago.
I found this formula helpful, but ended up having my running totals go to zero in months where I had no data, then reappearing in months with activity. Any recommendation on how to work around this?
This is excellent. May in know how you do the running total of subtotals. Lets say for a product over months(horizintal) if you need to show data like for first month, the first value, for second Month value of first month plus value of 2nd Month and for 3rd month value of 1st plus value of 2nd plus value of 3rd and so on...
Great video! For me it looks like a bug in the traditional pivot chart function but nevermind. Here is the formula shown in the video : =calculate(SUM(Table_with_data[value_field]),FILTER(ALLSELECTED(your_calendar[date]),ISONORAFTER(your_calendar[date],max(table_with_data[date]),desc))) It works also for cumulative count of values by replacing SUM in the formula with COUNT (or COUNTA for non numerical values)
Nice, thanks! Mynda, I couldn't watch the whole video since it gave an error on Mac that It will not work properly on Mac. My question to you is how to take out Total of Running Total? I have created my Pivot Table and got the Running Total but now at the end, I want it to show Total of Running Total? Thanks
The total of a running total is the same as the column total, so you don't need a running total to get this. Hopefully, I've understood your question. If not, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Simply amazing. Love hour channel. Question: how would one get the beginning -->month activities --> ending running running subtotals In pivot tables? Subject for another video ? 😁
How does the running total work, if you have year and quarter in columns. I'm trying to need grand total and also running total% of grand total when you have year and quarter in columns and the rows have different product lines. I'm trying to see the QoQ trend of each product sorted highest to lowest by revenue
I'm a beginner of Power Pivot: I downloaded the Excel file but didn't understand when and where you created the calendar table, I found it directly in data model but it doesn't exist in Excel sheets. Is it possible?
On the Design tab you can create a date table based on the dates in the model via the Date Table drop down > New. If you don't see this option then you have an old version of Excel that doesn't have it. If so, you can create one in Power Query or Excel.
Hi Mynda, a SOS question 🙋♀️ what if I want to calculate the running total of the last 4 weeks for a MEASURE value? I tried multiple ways, and couldn’t get it. Could you please help me?
Hi Mynda, Great video! One difference to the built in Show values as 'Running Total In..' is that the built in version repeats the value from the last month if the next month does not have any actuals values (or forecast or budget), while your version leaves an empty row and a gap in the graph. For example, if you remove the values for actuals and forecast for August 2020 in your example, you get a gap in the graph. Any way to fix the measure to keep the value from last month instead? David
Hi David, Yes, unfortunately, this formula doesn't allow for gaps in your dataset. I don't have a tutorial that explains the solution, so I recommend you post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, this running total measure DAX is really helpful. But how could I create a measure to calculate the difference of two consecutive rows in a power pivot table, say next row’s value minus current one? (My case is - my data table has records only when the plant generates data, thus the date or time is not continuous, or I will have some empty row if I use my calendar table to summarise data, as far as I know I cannot use DAX function like PREVIOUSDAY, EARLIER, etc). I am still not familiar with DAX functions, so could you make an episode to address the above mentioned issue for calculating difference of two role in a power pivot table by using DAX function. Thanks!
Hi Jill, I don't have any tutorials specifically on ISONORAFTER, but perhaps this breakdown of the function will help: learn.microsoft.com/en-us/dax/isonorafter-function-dax
Hi Mynda Great video! I've searched around a bit now and only find examples similar to yours. I have a problem to run totals by date and product. I've tried to combine different DAX formulas but the runing totals with DAX only work when I exclude product. I would really appreciate and hope other will to it too if you can guide or help me. Example: In the end of your demonstration you made in the pivottable you, dragged and drop dates to "row" and dragged and drop measures to "Values" field How about when you drag and drop anydata (for.eg products) to "columns" Best Regards
Great video! If there are two dates in the table i.e. Order date and shipped date, thus making one active and one inactive relationship, how can we get the RT result? Where do we place the USERELATIONSHIP formula? Thanks in advance!
Hi Mynda, how did you get the option for Actual(Date)? I assume that's referring to an Actual column in the date table? When I create a date table it doesn't add data columns into it.
Hi Mynda, When using this formula for a measure on a data set that is really long (160k+ rows) it takes a very long time to load. Is there a more complex formula/filter I can apply to make sure it is not looking at all of the data (every row) to make the chart/table? Currently it take 3 minutes to make the chart.
Thank you! In your example, what if you have forecast in 2022 but no actuals. How do you show zero for actuals RT in 2022 instead of repeating 301,845 . I would like to show where the RT stops in a year and not repeat the values.
PivotTables aggregate data. If you don't want to see the data aggregated, then you need to bring in the fields from all the columns to the row labels until such time as it is no longer able to aggregate any of the values.
hi, I want to make a RunningTotal using Pivot table, i've the following data: id Customer item SalesAmount 1 Global inc apple 50 2 Americana banana 10 3 Americana apple 100 4 Americana apple 60 5 Global inc apple 10 it's a very simple pivot table with the "id" field in the Rows section, and the "item" in the Filters section, and the "SalesAmount" in the Values section, i set the "Show Value As" of the SalesAmount column "Value Filed settings" to "Running Total in" and set the Base Field to "id" and it works fine, but i also want to show the customer, so i add this field to the Rows section after the "id" Field but as soon as i do this the running total column becomes distorted, as if the summing process reset when the customer change, or Excel auto group the data according to the customer despite that i set the Base Field of the running total column to id.
It's not always wrong, so there must be something specific to your scenario. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
It’s a great video, but I can’t believe that something so basic and simple as a running total in Excel is so complicated. There is no way an average user would know how to write that measure, I expect everyone that watches this video copies it from your video.
For short, you are a genius instructor!
Thank you Thank you Thank you . . .
Thanks so much 😊
You’re the best I’ve came across btw.
Explanations, definitions, professional terse intro with oblivious depth and passion is how I like it. Your dashboard training pretty much catapulted my career.
My running totals stop at month ends, then rests every month. My lookup date column is week ending dates only. I wonder if that’s why.
John
Texas
Wow, that's amazing to hear, John! Not sure what's causing your RT to reset each month. You can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub It works. Thanks!
I’ve seen many masters in excel…but you are outstanding. You are doing with so confidently, we have to pause the video to understand.
Curious to know how long you are using this excel ?
Thanks for your kind words 😊 I’ve been using Excel for nearly 30 years 😉 but really my skills improved significantly when I started teaching others about 12 years ago.
@@MyOnlineTrainingHub Holy molly you look like you are in your thirties lol
Wow Mynda, this is a great example. Something I definitely want to try at work.
So pleased you found it helpful, Dianne!
I found this formula helpful, but ended up having my running totals go to zero in months where I had no data, then reappearing in months with activity. Any recommendation on how to work around this?
Hi - thank you for this beautiful DAX running total explanation - straight to the point, very clear instructions - :)
Glad it was helpful, Martin!
Excellent video. Why at 4:00 do you choose Descending order? Aren't they ascending from oldest to newest? Thank you.
thank you ! you're the mentor I wish I have. God bless you
Glad you found my video helpful :-)
@@MyOnlineTrainingHub Thanks Mynda!
This is excellent. May in know how you do the running total of subtotals. Lets say for a product over months(horizintal) if you need to show data like for first month, the first value, for second Month value of first month plus value of 2nd Month and for 3rd month value of 1st plus value of 2nd plus value of 3rd and so on...
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
hi Mynda, thanx for posting, would it be possible to download the file?
My pleasure! The file download link is available at the end of the video. However, I've also posted it in the video description now.
Your tips are outstanding
Glad you think so! 🙏😊
Awesome example Mynda! Thanks for demonstrating this technique :)) Thumbs up!!
Thanks, Wayne!
Great video! For me it looks like a bug in the traditional pivot chart function but nevermind.
Here is the formula shown in the video :
=calculate(SUM(Table_with_data[value_field]),FILTER(ALLSELECTED(your_calendar[date]),ISONORAFTER(your_calendar[date],max(table_with_data[date]),desc)))
It works also for cumulative count of values by replacing SUM in the formula with COUNT (or COUNTA for non numerical values)
Glad you enjoyed it!
Nice, thanks! Mynda, I couldn't watch the whole video since it gave an error on Mac that It will not work properly on Mac. My question to you is how to take out Total of Running Total? I have created my Pivot Table and got the Running Total but now at the end, I want it to show Total of Running Total? Thanks
The total of a running total is the same as the column total, so you don't need a running total to get this. Hopefully, I've understood your question. If not, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Simply amazing. Love hour channel.
Question: how would one get the beginning -->month activities --> ending running running subtotals In pivot tables? Subject for another video ? 😁
Thank you! Yes, a topic for another video perhaps 🤔
How does the running total work, if you have year and quarter in columns. I'm trying to need grand total and also running total% of grand total when you have year and quarter in columns and the rows have different product lines. I'm trying to see the QoQ trend of each product sorted highest to lowest by revenue
Great video Mynda - have a great weekend - greetings from Germany
Cheers, Heiko! Hope you have a great weekend too 😊
I'm a beginner of Power Pivot: I downloaded the Excel file but didn't understand when and where you created the calendar table, I found it directly in data model but it doesn't exist in Excel sheets. Is it possible?
On the Design tab you can create a date table based on the dates in the model via the Date Table drop down > New. If you don't see this option then you have an old version of Excel that doesn't have it. If so, you can create one in Power Query or Excel.
Hats off to you and your team.
Thanks so much!
Thanks for the great video. Does this work the same way when we want the running total along the bottom rather than down the side?
Yes, you can orientate the PivotTable with the running total going across columns.
Hi Mynda, a SOS question 🙋♀️ what if I want to calculate the running total of the last 4 weeks for a MEASURE value? I tried multiple ways, and couldn’t get it. Could you please help me?
Hi Mynda, Great video! One difference to the built in Show values as 'Running Total In..' is that the built in version repeats the value from the last month if the next month does not have any actuals values (or forecast or budget), while your version leaves an empty row and a gap in the graph. For example, if you remove the values for actuals and forecast for August 2020 in your example, you get a gap in the graph. Any way to fix the measure to keep the value from last month instead?
David
Hi David, Yes, unfortunately, this formula doesn't allow for gaps in your dataset. I don't have a tutorial that explains the solution, so I recommend you post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, this running total measure DAX is really helpful. But how could I create a measure to calculate the difference of two consecutive rows in a power pivot table, say next row’s value minus current one? (My case is - my data table has records only when the plant generates data, thus the date or time is not continuous, or I will have some empty row if I use my calendar table to summarise data, as far as I know I cannot use DAX function like PREVIOUSDAY, EARLIER, etc). I am still not familiar with DAX functions, so could you make an episode to address the above mentioned issue for calculating difference of two role in a power pivot table by using DAX function. Thanks!
hi Mynda, thanks for your video, however the part ISONORAFTER part is a little bit confusing, could you please kindly explain a little bit.
Hi Jill, I don't have any tutorials specifically on ISONORAFTER, but perhaps this breakdown of the function will help: learn.microsoft.com/en-us/dax/isonorafter-function-dax
Elegant soloution! Thank you!
Glad it was helpful!
Hi Mynda!Really Great And Helpful Tip...Thank You :)
Great to hear, Darryl!
This was great, thanks Mynda!
Thanks, Chris! 😊
Hi Mynda
Great video!
I've searched around a bit now and only find examples similar to yours. I have a problem to run totals by date and product. I've tried to combine different DAX formulas but the runing totals with DAX only work when I exclude product. I would really appreciate and hope other will to it too if you can guide or help me.
Example: In the end of your demonstration you made in the pivottable you,
dragged and drop dates to "row"
and dragged and drop measures to "Values" field
How about when you drag and drop anydata (for.eg products) to "columns"
Best Regards
Great video! If there are two dates in the table i.e. Order date and shipped date, thus making one active and one inactive relationship, how can we get the RT result? Where do we place the USERELATIONSHIP formula? Thanks in advance!
Thanks, George! You can use USERELATIONSHIP with CALCULATE like this:
=CALCULATE( expression,
USERELATIONSHIP(Table1[Column], Table2[Column]))
Nice one Mynda!
Thanks so much!
Hi Mynda, how did you get the option for Actual(Date)? I assume that's referring to an Actual column in the date table? When I create a date table it doesn't add data columns into it.
Actual[Date] date is the date column from the Actual table.
Hi Mynda, When using this formula for a measure on a data set that is really long (160k+ rows) it takes a very long time to load. Is there a more complex formula/filter I can apply to make sure it is not looking at all of the data (every row) to make the chart/table? Currently it take 3 minutes to make the chart.
Try just using the Show Values As > Running Total and removing the sub totals as you wouldn't need these for a chart anyway.
Is there a way to use the STDEV function in a pivot table custom formula? I keep getting #DIV/0 errors.
Thank you! In your example, what if you have forecast in 2022 but no actuals. How do you show zero for actuals RT in 2022 instead of repeating 301,845 . I would like to show where the RT stops in a year and not repeat the values.
Hi Jason, in my example the actuals stop in April and so does the running total, so it appears to do what you describe.
Well Said.. Keep on uploading new video
Thank you, I will :-)
Well explained! Thanks!
Glad you enjoyed it!
Hi Mynda, would it be possible to create a burndown chart which is almost the opposite of running totals?
Yes, it's possible, but I don't have any examples I can point you to.
What should we do if we want the pivot table show the value not sum or mean or any other modifications on the values of each row?
PivotTables aggregate data. If you don't want to see the data aggregated, then you need to bring in the fields from all the columns to the row labels until such time as it is no longer able to aggregate any of the values.
Thank You
You're welcome 😊
Great, it is a life saver!.
Great to hear, Alberto!
Can you show to create a "calendar table"?
I’ll add it to my list 😉
How do you know all this stuff...OMG, thank you.
You are so welcome 😊
How would you “reset” the running total for each year?
It would be dependent upon the date field, a chart filtering for 2021 data, would not show 2022. But another chart with the 2022 filter, would.
Great. Thanks
You are welcome!
This is great work. Interested in a job?
Thanks, Paul! This is my job 😉
This is so convoluted. Why didn't Excel just make this a feature? This is the way that running totals should be displayed!
I guess it's complicated to do that more easily.
how did you create the "Calendar table"?
Power Pivot window > Design tab > Date Table
@@MyOnlineTrainingHub yes thanks. but i was referring to your dimension table. how did you create that?
Using the steps in my previous reply. i.e. the Date Table button you'll find on the Design tab of the ribbon in the Power Pivot window.
@@MyOnlineTrainingHub oh wow. got it now. thank you very much.
hi,
I want to make a RunningTotal using Pivot table, i've the following data:
id Customer item SalesAmount
1 Global inc apple 50
2 Americana banana 10
3 Americana apple 100
4 Americana apple 60
5 Global inc apple 10
it's a very simple pivot table with the "id" field in the Rows section, and the "item" in the Filters section, and the "SalesAmount" in the Values section,
i set the "Show Value As" of the SalesAmount column "Value Filed settings" to "Running Total in" and set the Base Field to "id" and it works fine,
but i also want to show the customer, so i add this field to the Rows section after the "id" Field but as soon as i do this the running total column becomes distorted, as if the summing process reset when the customer change, or Excel auto group the data according to the customer despite that i set the Base Field of the running total column to id.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I tried to adjust your way for variables and it does not work :(
Fine, but it would be appropriate to have the working file to be able to work with the data.
If you watched to the end you'd have seen the file download link ;-) I've also put it in the description now.
Grand total in pivot table is always wrong why
It's not always wrong, so there must be something specific to your scenario. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
👍🏻
Cheers, Andrew!
👏🏻👏🏻👏🏻
Cheers, Graham!
It’s a great video, but I can’t believe that something so basic and simple as a running total in Excel is so complicated. There is no way an average user would know how to write that measure, I expect everyone that watches this video copies it from your video.
I agree, it does seem overly complicated for something so commonly required.
Thank you @MyndaTreacy
My pleasure! Thanks for watching 😊