Problems with PivotTable Running Totals and HOW TO FIX THEM

แชร์
ฝัง
  • เผยแพร่เมื่อ 23 ธ.ค. 2024

ความคิดเห็น • 109

  • @m.raedallulu4166
    @m.raedallulu4166 3 ปีที่แล้ว +1

    For short, you are a genius instructor!
    Thank you Thank you Thank you . . .

  • @diannedrechsler4792
    @diannedrechsler4792 3 ปีที่แล้ว +1

    Wow Mynda, this is a great example. Something I definitely want to try at work.

  • @johnbw2597
    @johnbw2597 2 ปีที่แล้ว +1

    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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 ปีที่แล้ว +1

      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

    • @johnbw2597
      @johnbw2597 2 ปีที่แล้ว

      @@MyOnlineTrainingHub It works. Thanks!

  • @martinpaulbannister1745
    @martinpaulbannister1745 3 ปีที่แล้ว +2

    Hi - thank you for this beautiful DAX running total explanation - straight to the point, very clear instructions - :)

  • @exceltricks-de
    @exceltricks-de 3 ปีที่แล้ว +1

    Great video Mynda - have a great weekend - greetings from Germany

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว

      Cheers, Heiko! Hope you have a great weekend too 😊

  • @wayneedmondson1065
    @wayneedmondson1065 3 ปีที่แล้ว

    Awesome example Mynda! Thanks for demonstrating this technique :)) Thumbs up!!

  • @sjd7aa
    @sjd7aa 7 หลายเดือนก่อน

    Excellent video. Why at 4:00 do you choose Descending order? Aren't they ascending from oldest to newest? Thank you.

  • @MultiMrDJ
    @MultiMrDJ ปีที่แล้ว +1

    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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  ปีที่แล้ว

      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

  • @hardikey
    @hardikey 3 ปีที่แล้ว +1

    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 ?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว +1

      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.

    • @Lyriks_
      @Lyriks_ 2 ปีที่แล้ว

      @@MyOnlineTrainingHub Holy molly you look like you are in your thirties lol

  • @johnbauer3158
    @johnbauer3158 2 ปีที่แล้ว +2

    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?

    • @matttreby1239
      @matttreby1239 หลายเดือนก่อน

      I also noticed this anomaly. It is a clearly a shortfall in the Fx measure created for running totals for actuals. I figured out a temp workaround by manually manipulating my actuals data table to add in "dummy" sales (zero figures), one for each customer for each month. A quick and easy task to do, but I do want to figure out a proper solution eventually. Once this is done the RT actual Fx measure works as it has actuals sales data for every month, not just select months.

  • @ursula9875
    @ursula9875 3 ปีที่แล้ว +3

    hi Mynda, thanx for posting, would it be possible to download the file?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว +1

      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.

  • @rsn29sep
    @rsn29sep 3 ปีที่แล้ว

    Hats off to you and your team.

  • @intanshaharudin9657
    @intanshaharudin9657 3 ปีที่แล้ว

    thank you ! you're the mentor I wish I have. God bless you

  • @lucalenzi9922
    @lucalenzi9922 2 ปีที่แล้ว +1

    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?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 ปีที่แล้ว +1

      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.

  • @shoppersdream
    @shoppersdream 7 หลายเดือนก่อน

    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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  7 หลายเดือนก่อน

      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

  • @ksu3001
    @ksu3001 3 ปีที่แล้ว

    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!

  • @Jill_Liu
    @Jill_Liu 2 ปีที่แล้ว

    hi Mynda, thanks for your video, however the part ISONORAFTER part is a little bit confusing, could you please kindly explain a little bit.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 ปีที่แล้ว

      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

  • @celestialcolors
    @celestialcolors 3 หลายเดือนก่อน

    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

  • @aliasghar09
    @aliasghar09 8 หลายเดือนก่อน

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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  8 หลายเดือนก่อน

      Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

  • @georgeaslanis7166
    @georgeaslanis7166 2 ปีที่แล้ว

    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!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 ปีที่แล้ว

      Thanks, George! You can use USERELATIONSHIP with CALCULATE like this:
      =CALCULATE( expression,
      USERELATIONSHIP(Table1[Column], Table2[Column]))

  • @jarrodtuxworth9670
    @jarrodtuxworth9670 2 ปีที่แล้ว

    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?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 ปีที่แล้ว

      Yes, you can orientate the PivotTable with the running total going across columns.

  • @stephanegarcia5567
    @stephanegarcia5567 11 หลายเดือนก่อน

    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)

  • @chrism9037
    @chrism9037 3 ปีที่แล้ว

    This was great, thanks Mynda!

  • @mesutunsal727
    @mesutunsal727 3 ปีที่แล้ว

    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

  • @sachinkishore4115
    @sachinkishore4115 ปีที่แล้ว

    Your tips are outstanding

  • @samwatson2843
    @samwatson2843 ปีที่แล้ว

    Elegant soloution! Thank you!

  • @darrylmorgan
    @darrylmorgan 3 ปีที่แล้ว

    Hi Mynda!Really Great And Helpful Tip...Thank You :)

  • @injunnear72
    @injunnear72 3 ปีที่แล้ว

    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.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว

      Hi Jason, in my example the actuals stop in April and so does the running total, so it appears to do what you describe.

  • @shangrila3165
    @shangrila3165 2 ปีที่แล้ว

    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?

  • @redpoo
    @redpoo 3 ปีที่แล้ว

    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.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว

      Actual[Date] date is the date column from the Actual table.

  • @alikoohi8265
    @alikoohi8265 2 ปีที่แล้ว

    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?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 ปีที่แล้ว

      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.

  • @Jay21121
    @Jay21121 3 ปีที่แล้ว

    Is there a way to use the STDEV function in a pivot table custom formula? I keep getting #DIV/0 errors.

  • @vincentrovedatti8677
    @vincentrovedatti8677 3 ปีที่แล้ว

    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.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว

      Try just using the Show Values As > Running Total and removing the sub totals as you wouldn't need these for a chart anyway.

  • @jamespaterson4529
    @jamespaterson4529 3 ปีที่แล้ว

    Hi Mynda, would it be possible to create a burndown chart which is almost the opposite of running totals?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว

      Yes, it's possible, but I don't have any examples I can point you to.

  • @MrNoBSgiven
    @MrNoBSgiven 3 ปีที่แล้ว

    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 ? 😁

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว

      Thank you! Yes, a topic for another video perhaps 🤔

  • @zulhairibaba
    @zulhairibaba 3 ปีที่แล้ว

    Can you show to create a "calendar table"?

  • @nigelsapalit43
    @nigelsapalit43 3 ปีที่แล้ว

    Well Said.. Keep on uploading new video

  • @Acheiropoietos
    @Acheiropoietos 3 ปีที่แล้ว

    Nice one Mynda!

  • @KKB-tt4lj
    @KKB-tt4lj 3 ปีที่แล้ว

    Well explained! Thanks!

  • @tobiisagoodboy7935
    @tobiisagoodboy7935 3 ปีที่แล้ว

    how did you create the "Calendar table"?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว

      Power Pivot window > Design tab > Date Table

    • @tobiisagoodboy7935
      @tobiisagoodboy7935 3 ปีที่แล้ว

      @@MyOnlineTrainingHub yes thanks. but i was referring to your dimension table. how did you create that?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว

      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.

    • @tobiisagoodboy7935
      @tobiisagoodboy7935 3 ปีที่แล้ว +1

      @@MyOnlineTrainingHub oh wow. got it now. thank you very much.

  • @edsta714
    @edsta714 3 ปีที่แล้ว

    How would you “reset” the running total for each year?

    • @Acheiropoietos
      @Acheiropoietos 3 ปีที่แล้ว

      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.

  •  2 ปีที่แล้ว

    Great, it is a life saver!.

  • @danushairan
    @danushairan 7 หลายเดือนก่อน

    I tried to adjust your way for variables and it does not work :(

  • @seanain1
    @seanain1 2 ปีที่แล้ว

    Thank You

  • @physicsmostafa1774
    @physicsmostafa1774 ปีที่แล้ว

    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.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  ปีที่แล้ว

      Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

  • @sureshvenkatesh886
    @sureshvenkatesh886 2 ปีที่แล้ว

    Grand total in pivot table is always wrong why

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 ปีที่แล้ว

      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

  • @Trucpq
    @Trucpq 3 ปีที่แล้ว

    Great. Thanks

  • @SuzukiKid400
    @SuzukiKid400 2 ปีที่แล้ว

    This is so convoluted. Why didn't Excel just make this a feature? This is the way that running totals should be displayed!

  • @regihamp0206
    @regihamp0206 7 หลายเดือนก่อน

    How do you know all this stuff...OMG, thank you.

  • @IvanCortinas_ES
    @IvanCortinas_ES 3 ปีที่แล้ว

    Fine, but it would be appropriate to have the working file to be able to work with the data.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 ปีที่แล้ว +1

      If you watched to the end you'd have seen the file download link ;-) I've also put it in the description now.

  • @PaulRichards1
    @PaulRichards1 3 ปีที่แล้ว

    This is great work. Interested in a job?

  • @zabrus24
    @zabrus24 3 ปีที่แล้ว

    👍🏻

  • @grahamparker7729
    @grahamparker7729 3 ปีที่แล้ว

    👏🏻👏🏻👏🏻

  • @davidgray5764
    @davidgray5764 11 หลายเดือนก่อน

    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.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  11 หลายเดือนก่อน

      I agree, it does seem overly complicated for something so commonly required.

  • @victorsamgichuhi2486
    @victorsamgichuhi2486 ปีที่แล้ว

    Thank you @MyndaTreacy