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