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

[Gervasio1977]

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

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]

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

[#3]

@Gervasio1977 

Creating PivotTable I'd add data to data model

and with it for the resulting data as

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