GETPIVOTDATA: is there a way to define an Item as "All" or "*"?

%3CLINGO-SUB%20id%3D%22lingo-sub-2416816%22%20slang%3D%22en-US%22%3EGETPIVOTDATA%3A%20is%20there%20a%20way%20to%20define%20an%20Item%26nbsp%3Bas%20%22All%22%20or%20%22*%22%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416816%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20want%20to%20use%20GetPivotData%20to%20get%20numbers%20from%20a%20Pivot%20(well%2C%20that's%20what%20it%20is%20for%2C%20isn't%20it%3F)%2C%20but%20I%20want%20to%20get%20summarized%20and%20non-summarised%20values%20using%20the%20%3CEM%3E1%20single%20and%20elegant%20formula%3C%2FEM%3E.%20In%20other%20words%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConsidering%20that%20its%20syntax%20is%20%3CSTRONG%3EGETPIVOTDATA(data_field%2C%20pivot_table%2C%20%5Bfield1%2C%20%3CFONT%20color%3D%22%230000FF%22%3Eitem1%3C%2FFONT%3E%2C%20field2%2C%20%3CFONT%20color%3D%22%230000FF%22%3Eitem2%3C%2FFONT%3E%5D%2C%20...)%3C%2FSTRONG%3E%2C%20is%20there%20a%20way%20to%20define%20an%20%3CFONT%20color%3D%22%230000FF%22%3EItem%3C%2FFONT%3E%26nbsp%3Bas%20%3CFONT%20color%3D%22%23FF0000%22%3E%22%3CSTRONG%3EAll%3C%2FSTRONG%3E%22%3C%2FFONT%3E%20or%20%3CFONT%20color%3D%22%23FF0000%22%3E%22%3CSTRONG%3E*%3C%2FSTRONG%3E%22%3C%2FFONT%3E%3F%3F%3F%20It%20would%20solve%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20you%20find%20an%20example%20of%20what%20I%20need.%20To%20be%20clear%3A%20my%20real%20problem%20is%20far%20more%20complex%20than%20this%2C%20with%20much%20more%20data%20and%20much%20more%20degrees%20of%20hierarchy.%20So%2C%20I%20have%20limited%20room%20to%20change%20Pivot%20Table%20or%20final%20output%20layouts...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Gervasio1977_0-1622823029589.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286309i35CEB1735ACBEB71%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Gervasio1977_0-1622823029589.png%22%20alt%3D%22Gervasio1977_0-1622823029589.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENotice%20that%20GetPivotData%20is%20returning%20%23REF!%20for%20some%20cells%20in%20my%20output%20table.%20Of%20course%20it%20is%20and%20I%20know%20why%3A%20there%20is%20no%26nbsp%3B%22SubCategory%22%3D%22Total%22.%20How%20can%20I%20make%20the%20formula%20to%20understand%20that%20I%20want%20in%20this%20cell%20the%20total%20of%20%22Category%22%20using%20one%20single%20formula%20to%20all%20this%20column%3F%20It%20has%20to%20be%20a%20formula%20that%20understands%20when%20I%20want%20consolidations%20and%20when%20I%20don't...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20the%20final%20problem%20is%20far%20more%20complex%20than%20this.%20In%20this%20simple%20example%2C%20I%20can%20do%20it%20like%20%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%22if%20(SubCategory%20is%20%22Total%22%3B%20GetPivotData%20up%20to%20%22Category%22%3B%26nbsp%3BGetPivotData%20up%20to%20%22SubCategory%22)%22%3C%2FEM%3E%3C%2FFONT%3E.%20However%2C%20in%20a%20more%20complex%20scenario%2C%20it%20would%20result%20in%20an%20incredibly%20huge%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%2C%20somebody%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2416816%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2416893%22%20slang%3D%22en-US%22%3ERe%3A%20GETPIVOTDATA%3A%20is%20there%20a%20way%20to%20define%20an%20Item%26nbsp%3Bas%20%22All%22%20or%20%22*%22%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416893%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1071282%22%20target%3D%22_blank%22%3E%40Gervasio1977%3C%2FA%3E%26nbsp%3BWhy%20insist%20on%20GETPIVOTDATA%20when%20you%20achieve%20the%20same%20by%20formatting%20the%20pivot%20table%20in%20a%20different%20way.%20On%20the%20design%20tab%2C%20choose%20Report%20Layout%20%22Tabular%22%20and%20%22Repeat%20All%20Item%20Labels%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

I want to use GetPivotData to get numbers from a Pivot (well, that's what it is for, isn't it?), but I want to get summarized and non-summarised values using the 1 single and elegant formula. In other words:

 

Considering that its syntax is GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...), is there a way to define an Item as "All" or "*"??? It would solve my problem.

 

Here you find an example of what I need. To be clear: my real problem is far more complex than this, with much more data and much more degrees of hierarchy. So, I have limited room to change Pivot Table or final output layouts...

 

Gervasio1977_0-1622823029589.png

Notice that GetPivotData is returning #REF! for some cells in my output table. Of course it is and I know why: there is no "SubCategory"="Total". How can I make the formula to understand that I want in this cell the total of "Category" using one single formula to all this column? It has to be a formula that understands when I want consolidations and when I don't...

 

Again, the final problem is far more complex than this. In this simple example, I can do it like "if (SubCategory is "Total"; GetPivotData up to "Category"; GetPivotData up to "SubCategory")". However, in a more complex scenario, it would result in an incredibly huge formula.

 

I hope, somebody could help.

2 Replies

@Gervasio1977 Why insist on GETPIVOTDATA when you achieve the same by formatting the pivot table in a different way. On the design tab, choose Report Layout "Tabular" and "Repeat All Item Labels".

 

See attached.

 

@Gervasio1977 

Creating PivotTable I'd add data to data model

image.png

and with it for the resulting data as

image.png

in P24

=CUBEVALUE("ThisWorkbookDataModel",
  CUBEMEMBER("ThisWorkbookDataModel",
     "[Range].[" &
     IF(O24="Total",
     IF(N24="Total",
         "SubCategory]",
         "Category].[" & N24 & "]"),
      "SubCategory].["&O24&"]")),
    "[Measures].[Sum of Sales]")

and drag it down