Jun 04 2021 09:39 AM
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.
Jun 04 2021 09:56 AM
@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.
Jun 04 2021 10:37 AM
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