Dynamic Reference to Data in Pivot Table (part of data model)
Our take
Hi!
I have an excel with a big source data table (66k rows). I use pivot tables to summarize the data, and as normal pivot tables didn't show the data correctly, I always selected "add data to data model", and then it worked. Allthough the pivot tables look the same, they seem to work quite differently.
I now want to look up certain values in these new pivot tables. Specifically, I have a table with DoI on one axis and the year on the other axis, and I want dynamic references, so that I can drag the formula. How do I need to write it?
The table is directly linked to charts on a powerpoint, that's why I need it - there are some charts that are too complicated to link it directly to a pivot table.
Here's an example, the X$8 and the $W9 are the references I would usually use.
=GETPIVOTDATA("[Measures].[Sum of Value]",$D$8, "[PowerQuery_ALL_DATA_LONG_FORMAT].[Data Type]","[PowerQuery_ALL_DATA_LONG_FORMAT].[Data Type].&[Main Data]", "[PowerQuery_ALL_DATA_LONG_FORMAT].[Year]","[PowerQuery_ALL_DATA_LONG_FORMAT].[Year].&[X$8]", "[PowerQuery_ALL_DATA_LONG_FORMAT].[Status]","[PowerQuery_ALL_DATA_LONG_FORMAT].[Status].&[$W9]")
Thanks for your help!
Edit: I use MS 365, excel version 2511
Edit: this is how I would write it for a normal Pivot table, but it doesn't work in this case: =GETPIVOTDATA("Value",$D$8,"Data Type","Main Data","Status",$W9,"Year",X$8)
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pivot Calculated Field issueHi guys, Im still learning excel and im having a hard time inserting a formula in a pivot table that initially created in a manual table. I wanted to to utilize the pivot function of opening a new sheet with the filtered values + the formulas that I want to show. Here's a link to the sample data set that i'm using. Here's the breakdown: have 200 transactions over a 3 day period (no dates for this one sadly) with a sudden decline in approval rate divided it into 2 groups (first half, last half) Wanted to make a pivot table checking the approval rates of each of the following: Card type, Country, MID (merchant ID). This is just to investigate the root cause. I already have a sample formula for the Card type part but having trouble inserting it in a pivot table. It should be easy to apply to other pivot tables once I get this one done =(COUNTIFS(Raw!H:H,"First 100",Raw!B:B,"Approved",Raw!D:D,"Amex")/COUNTIFS(Raw!H:H,"First 100",Raw!D:D,"Amex") submitted by /u/NierraSol [link] [comments]
- Power Pivot and Data Models - updating data? Switching out what's behind pivot?I'm trying to learn Power Pivot so I can use larger and different data sets using data models. For now, I only care about a single large data set (though I ultimately have others too) and a corresponding pivot table. The data set is in the data model as "load to" connection only and add to data model. I re-ran that data set updated and want to do what effectively I do with Excel datasets, just copy paste over the first and refresh the pivot table. I can't delete the original query and rename the new one and repoint because the pivot table automatically is cleared. I don't know if there's any other way to remove the original data set and overwrite with the new? I have one measure or calculated field in the data that I did recreate. I want to avoid recreating the pivot every time because that's inefficient. How do I approach this? submitted by /u/JayReddt [link] [comments]
- How can I create a pivot table with the PowerPivot Measure being used for the rows?Building an inventory valuation model. Basically, taking data from sales, warehouse inventory, and stores to find lower of costs/sales value. I'm also applying a obsolescence formula that basically just says "if we sold 12.5% of our all available units over the year, then mark it as current, otherwise mark it as the last date we received it in the warehouse". This measure is named "Slow moving date". I'd like to use "slow moving date" as a row in a pivot table, but Excel says that value can't be a row. How can I force it to allow the dates as a row without having to copy/paste numbers only from a different pivot to make it work? submitted by /u/scaredycat_z [link] [comments]
- Creating a graphic with custom located cells populated by a pivot tableI want to create a graphic with drop down menus that work off of each. So, I select the "Bore size" and the "Class". After I want custom placed cells to populate with the appropriate information based on a separate worksheet that has all of the information for each value. (First Picture Below) Red represents my drop down menus. Once I have populated that, I have a separate spreadsheet (pictured below) with the columns detailed for which variable they are to fill, and it will give me my values accordingly. What terms and capabilities with Excel should I be researching to be able to achieve this? I'm assuming "Pivot Tables" for referencing the information from Sheet 2 to Sheet 1, but I don't know how to get the information to place itself appropriately into the blue sections. Do I have to merge cells to fit there, or can I draw custom cell placement? TIA Also, does anyone know what you would call this type of chart in Excel? The best answer I have so far is "Lookup-Driven Dashboard". Sheet 1 https://preview.redd.it/o4ptbz0ayvmg1.png?width=1325&format=png&auto=webp&s=bddd06bc28023fba707942fd7bdade6341447e7d Sheet 2 https://preview.redd.it/3odfm92ryvmg1.png?width=472&format=png&auto=webp&s=a7c3c27880a79f63916ccc8ff5a1064458bcd239 submitted by /u/West-Doubt6824 [link] [comments]