PDA

View Full Version : GETPIVOTDATA formula with multiple criteria

dluhut
2017-05-29, 06:41 PM
I have the following Array GETPIVOTDATA function that takes multiple criteria from a regular pivot table.

{=SUM(IFERROR(GETPIVOTDATA("Amount",sap_bw_pivot!\$A\$3,"Profit Center",{"10891100";"10891200"},"Functional area",{"Z000","Z001","Z002","Z003","Z005","Z006","Z007"},"G/L Account",TEXT(\$C130,"000000"),"Fiscal year/period",YEAR(I\$8)&"-"&TEXT(MONTH(I\$8),"000"),"Actual/Plan/Variance","Actual"),0))}

May I know how can I make the same formula on the POWERPIVOT?

The reason I ask is that, on a power pivot, the GETPIVOTDATA has a square bracket [], and in that square brackets, is the 'criteria' that we'd like to search, which can only return 1 value.

=GETPIVOTDATA("[Measures].[Sum of Amount]",'2017sapbw-data-powerpivot (MTD)'!\$B\$3,"[2017sapbw].[Profit Center]","[2017sapbw].[Profit Center].&[10000100]","[2017sapbw].[G/L Account]","[2017sapbw].[G/L Account].&[620860]","[2017sapbw].[Functional area]","[2017sapbw].[Functional area].&[Z003]","[2017sapbw].[Fiscal year/period]","[2017sapbw].[Fiscal year/period].&[2017-001]","[2017sapbw].[Actual/Budget]","[2017sapbw].[Actual/Budget].&[Actual]")

So my question is, how can I change the red (profit center) and blue colored (functional area) for multiple criteria values?

AliGW
2017-05-29, 06:53 PM
Please note this forum's stance on cross-posting and amend your opening post accordingly: http://www.excelguru.ca/content.php?184