Dynamic GETPIVOTDATA Formulas for Power Pivot - the PROPER way!

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

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

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

    Mynda, you can simplify complex things so easily! I had trouble understanding GETPIVOTDATA, but this tutorial made it clear. Thank you! 😊👍

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

    Thank you so much for this video!!! I've spent hours trying to figure out how I can make my GETPIVOTDATA dynamic now that I am using powerpivot and this is the only video that have clearly explained a solution!! Thank you so much!!!!

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

    I love these tips... Things that seem almost obvious once they are explained so well, and you understand why... Thanks so much!

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

    It is so nice i was doing manually in our monthly report you save my lot's of time. Thanks

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

    Merry Christmas Mynda, Phil & team. Thanks so much for the year's tips and all the work you do. I'm looking forward to more in the New Year and wish you all the best for the years ahead. Stay safe xx

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

    Thanks! Was having so much trouble to find the right way to have this dynamic values for power query pivot tables! You helped a ton!

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

    Happy Holidays, Mynda! Thank you for all you do :)

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

    Thank you much Mynda! Been having issue with my dashboard's headline, pivot data moves when I click on a slicer. Funny, all I needed was to enable that generate GetPivotData option. You really save the day(s).

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

    I was stuck with this problem fur last couple of days. Now I know how to come around. Thanks a lot

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

    Hi Mynda. What a super tip! So easy, once you demonstrate how it is done. Thanks for sharing :)) Thumbs up!!

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

    Great. I was looking for an easy way to get the values for the most recent date on some data... This is perfect !

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

    Many thanks, Mynda.

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

    Mynda, thank you for this amazing video! This was so helpful in understanding how to use getpivotdata for a data model table.
    I see that you showed us how to change the formula so that it was reading the months from a referenced cell, do you have any advice on doing that for a reference cell that contains a date?
    I keep getting #REF! when I try to replace a date with a cell that contains a date. I am following the syntax you shared. The source table has the date formatted as a date so I don't understand why the data model table does not have the value formatted that way.

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

      When working with dates in PivotTables I always use my mouse to have Excel write the GETPIVOTDATA formula, then I can see the date structure it's expecting and make sure the cells I want to reference are in this same format.

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

    Hi this is a fab video, I finally understand the breakdown of this function. I'm wondering how I can get the data from the pivot table for year based on todays date?, where my row is year. any help appreciated

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

      Can't say without seeing your file, but the easiest thing to do is type = in an empty cell and then click on the value in the PivotTable you want. This will show you the formula syntax and from there you can reconstruct it with cell references.

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

    Thank you. This was so much needed.

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

    I have been following your videos - Very helpful for me. I am a retired VP and manage my own stock portfolio. I have several sheets in my work book that use a column of stock symbols in separate tables. When I add or subtract a stock in say the master table I would like all of the tables to update with the new stock entry. I have tried many methods with no success - very frustrating for me. Note: I am not a pivot table user. Regards and thanks.

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

      Glad you find my videos helpful 😊 your source data would be better in a single table. You can use Power Query to automate the gathering of the data into a single table: www.myonlinetraininghub.com/power-query-consolidate-excel-sheets

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

    THANK YOU FOR THIS!, I was just about to give up until I came across this video!

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

    Wouldn’t Cube formulas be better if it’s in the data model?

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

      I think so. I usually use cube function instead of getpivotdata function.

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

      Yea it would be good to show why/when it's better to use this vs the cube formula. I assume they each have different uses, I tend to just use cube formulas all the time

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

      Cube formulas definitely have advantages over GETPIVOTDATA, but they’re also a bit harder to learn.

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

    Hi Mynda!Great Tutorial,Really Cool Tip To Make The Formula Dynamic...Thank You :)

  • @almighty.RTD.1
    @almighty.RTD.1 ปีที่แล้ว

    Do you have a video on how to pull values from a PowerPivot table into another sheet in the same workbook? I have my powerpivot table set up but have another sheet that I would like to pull values from one of the columns into another sheet. I’ve tried everything.

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

      The only way is to load the data via a Power Pivot PivotTable. If you put all the columns from the Table in the PivotTable, it will show you all the data. Change the layout to 'Tabular' and remove subtotals and grand totals, and you have yourself a table.

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

    This is truly amazing! I really love the getpivot function.

  • @Gary-ln3td
    @Gary-ln3td ปีที่แล้ว

    Thanks for the video. Is there a way to create a normal pivot table from the PowerQuery data, and then use the getpivotdata formula? I ask because the formula is much cleaner to make dynamic in various ways and also user friendly to read/understand that way, compared to the beastly PowerQuery getpivotdata formula.

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

      Yes, you can use GETPIVOTDATA with regular PivotTables as explained here: th-cam.com/video/LDf_ORnSCu4/w-d-xo.html

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

    This was so useful. Thank you

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

    Love your videos! Saves so much time! 🙂
    Thanks a ton!!

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

    Excellent explanation!!! Thanks a lot !

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

    Great video and very helpful. I tend to avoid pivot tables but you videos are making me rethink that stance. Shout out for Canadian data. :-)

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

    Works great for stabilizing formating in charts!

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

    Great video thanks Mynda!

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

    Amazing. Thank for these tips

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

    Nailed it! Thank you very much!

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

    Thank you for the Video, may I know how can I reference a "range" into the argument? I would like to run a Max array function and return the value in this case

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

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

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

    Great tips! Thank you for your clear explanations and the Canadian data shout-out!!
    Just wanted to point out that Saskatchewan is misspelled. Right now it says "Saskachewan" (you are missing the "T").

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

      Oops! Thanks for the correction. I can't remember where I got that data from.

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

    Great! Can you make some videos about cubo functions? Thanks

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

    Thank you. I have a question. How can I use this to get value under the date format?

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

      It depends on whether you're summarising the data by day, month, quarter, year etc. However, if you type = and then click on the value field you want, you'll see the syntax required for the date that value field relates to and from there you can modify the formula to refer to dynamic cells.

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

    I have been using the formula in the way you described. I just wish it was as easy as a regular pivot table.

  • @hongshengma
    @hongshengma 24 วันที่ผ่านมา

    Hello Mynda, thanks a lot for this video. It worked very good with the dynamic text. But didn’t work with numbers, For example instead the month „Jan”, “Feb” I wanted to use year 2022, 2023, 2024. I built the similar formula but came out with Error as result.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  23 วันที่ผ่านมา

      When referencing dates, I recommend you use Excel to write the GETPIVOTDATA formula so you can see how it references them, then you can modify it accordingly to make it dynamic. e.g. the text label 'Jan' in the PivotTable is usually referred to in the formula as a 1.

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

    Hi Mynda, I have a technical question :) and I hope you'll help me clear it out: could you please tell me what kind of audio setup you're using for recording your videos? Microphone, recorder, any other important ingredient for obtaining such a wonderful clarity of your recorded sound? Something specific to pay attention to, when going out shopping for this kind of equipment? Any advice on that? :) Thank you very much in advance!

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

      Hi Mihaela, I'm using a Samson G-Track microphone and I use Camtasia Studio to record my videos. Hope that helps :-)

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

      @@MyOnlineTrainingHub Yes, thank you very much!

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

    Love it! Thanks for sharing!

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

    Great as and valuable tips 👍

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

    you save my life

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

    Helpful video. Thanks :)

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

    Thanks, Minda! Very Nice! Can you please help me with this issue? Somebody created a very complicated Pivot Table and I am trying to understand that Pivot Table. I added a custom field as Subtotal in the PT( total of 4 columns-ColumnName4) + ( total of 3 columns-ColumnName3). I used PowerPivot and Data Model and now Custom Field has become a separate Excel table and I want it to become a part of the existing Pivot table. (None of the fields match with the Data Source because there are a lot of aggregate functions used and renamed columns) Dealing with this Data Source is a nightmare. In your example, this was not a simple Pivot Table. Please help me. I just want to show the Sum of 2 Columns from PT and want that custom field to become a part of this PT. Thanks

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

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

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

    Very helpful video. Thanks

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

    Useful. I had actually switched this feature off as it now reads B3, B4 etc on = Cell ...as I didn't really appreciate this concept!

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

    Hi, could you possible do a dividend sheet, where you could monitor dividends automatically from a dashboard? I´ve been trying to create a dashboard like that to better monitor my stocks, how much I will get, when I will get it and so on, but I don´t know how to implement the data automatically.

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

      I don't know of a single reliable source of dividend data that will allow you to automate the gathering of this information.

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

    Is there a way to exclude Nov and December if you choose Newfoundland?

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

      Sure, you could wrap the formula in an IF that checks if the result = 0 and then return blank if it does.

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

    Helpful, Great work.

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

    Hello, can I reduce the informatiob that shown after drilling the pivot table, for ex,
    I have a table with name, age, gender, and I insert a pivot table and I want to show only name, and age after drill down the result in the pivot.
    Thank you.

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

      Maybe. It's difficult to visualise what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

  • @मृणू
    @मृणू 2 ปีที่แล้ว

    Hi
    I have a requirement where in i have to enter getpivot formula multiple time for set of combination.
    E.g.using the pivot shown in video
    In cell i want to have data for 3 items from row and 3 items from columns but in one cell.
    Is there any easy way. Since i have to enter i have to manual copy paste the formula in cell and then change the range to get correct data in particular cell. Its very tds.

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

    Hi Mynda, should this work if i have a pivottable directly sourcing data from a Power Bi dataset? I cant seem to get it to work, despite when hovering over a month i am trying to reference to from a cell the yellow formula preview renders correctly but the formula still results in a #REF! error.

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

      Not sure what you mean by 'the yellow formula preview'. You should be able to type = and then click on the value field in the PivotTable to get the GETPIVOTDATA formula to populate. From there you can edit the formula. If you're still stuck, you're welcome post your question and sample Excel file/screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

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

    i love that
    really helped me :D

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

    Thank you very much

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

    amazing thank you so much

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

    Can you please make a video describing - how to make date dynamic -in power pivot - pivot table. Date part of formula says
    [GetPivotData_PowerPivot].[Date]","[GetPivotData_PowerPivot].[Date].&[2024-04-02T00:00:00]" If you can make a video for us, it will be very helpful. Regards

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

      You can modify the formula like so where the date is in cell C4:
      [GetPivotData_PowerPivot].[Date]","[GetPivotData_PowerPivot].[Date].&["&TEXT(C4,"yyyy-mm-dd")&"T00:00:00]")

  • @PrinceAckah-h9k
    @PrinceAckah-h9k หลายเดือนก่อน

    Thanks. You saved my wall from my head banging against it.

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

    Can you make this dynamic for a normal pivot table?

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

      Yes. See my GETPIVOTDATA for regular PivotTables tutorial: www.myonlinetraininghub.com/excel-getpivotdata-function

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

    Great..! I like this tutorial..

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

    Helpful 💯

  • @k-mark9187
    @k-mark9187 2 ปีที่แล้ว

    Can you reference the field though? YOu are only making the item dynamic

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

      You can reference any value area cell. If you want to query the data model directly, then check out CUBE functions: th-cam.com/video/Wvajqz7uBbs/w-d-xo.html

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

    you're a lifesaver! thank you!

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

    Thanks...

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

    Can you make a video that shows how to deal with balance sheet vs income statement variables in your data set? For example, let's say I have monthly data for both my IS and BS but I want to show total 2021 in a pivot table. I want the pivot table to add up all the IS variables, but I only want the BS variable to show the YE 21 ending value, not the sum of every balance from every month of the year. Is there a way to do that?

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

      It's tricky with PivotTables to segregate data like that. You might be best to use CUBE functions or GETPIVOTDATA to build your reports so you can pick and choose the figures you want in your reports.

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

      @@MyOnlineTrainingHub Thanks, I've been wondering if I'm missing something obvious.

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

      You can change the BS calculation to only display the current period, thus only returning the sum of one value.

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

    Love you and your videos Mynda 🥹. Thank you so much!!!