Forum Discussion

Gervasio1977's avatar
Gervasio1977
Copper Contributor
Jun 04, 2021

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

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.

excel

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 04, 2021

    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

Resources