Problems with PivotTable Running Totals and HOW TO FIX THEM
Vložit
- čas přidán 30. 07. 2024
- PivotTable running totals just don't make sense sometimes. In this video I'll show you why and how to fix them.
Download the Excel file here: www.myonlinetraininghub.com/p...
0:13 PivotTable running total example
0:26 Regular PivotTable Running Total Limitations
2:14 Running Total Power Piovt Measure
6:02 Running Total Pivot Chart
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy - Věda a technologie
Wow Mynda, this is a great example. Something I definitely want to try at work.
So pleased you found it helpful, Dianne!
Hi - thank you for this beautiful DAX running total explanation - straight to the point, very clear instructions - :)
Glad it was helpful, Martin!
For short, you are a genius instructor!
Thank you Thank you Thank you . . .
Thanks so much 😊
Awesome example Mynda! Thanks for demonstrating this technique :)) Thumbs up!!
Thanks, Wayne!
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!
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!
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
Hi Mynda!Really Great And Helpful Tip...Thank You :)
Great to hear, Darryl!
This was great, thanks Mynda!
Thanks, Chris! 😊
Hats off to you and your team.
Thanks so much!
Great video Mynda - have a great weekend - greetings from Germany
Cheers, Heiko! Hope you have a great weekend too 😊
thank you ! you're the mentor I wish I have. God bless you
Glad you found my video helpful :-)
@@MyOnlineTrainingHub Thanks Mynda!
Elegant soloution! Thank you!
Glad it was helpful!
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?
Well explained! Thanks!
Glad you enjoyed it!
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?
Your tips are outstanding
Glad you think so! 🙏😊
Nice one Mynda!
Thanks so much!
Excellent video. Why at 4:00 do you choose Descending order? Aren't they ascending from oldest to newest? Thank you.
Is there a way to use the STDEV function in a pivot table custom formula? I keep getting #DIV/0 errors.
Great, it is a life saver!.
Great to hear, Alberto!
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]))
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.
Well Said.. Keep on uploading new video
Thank you, I will :-)
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.
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, 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, 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
Thank You
You're welcome 😊
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.
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 🤔
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!
Great. Thanks
You are welcome!
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.
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
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
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
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.
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
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.
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.
Can you show to create a "calendar table"?
I’ll add it to my list 😉
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.
I tried to adjust your way for variables and it does not work :(
How do you know all this stuff...OMG, thank you.
You are so welcome 😊
This is great work. Interested in a job?
Thanks, Paul! This is my job 😉
👏🏻👏🏻👏🏻
Cheers, Graham!
👍🏻
Cheers, Andrew!
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
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.
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.
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
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 😊