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

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