I have the following Array GETPIVOTDATA function that takes multiple criteria from a regular pivot table.
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.
So my question is, how can I change the red (profit center) and blue colored (functional area) for multiple criteria values?
Code:
{=SUM(IFERROR(GETPIVOTDATA("Amount",sap_bw_pivot!$A$3,"Profit Center",[B][COLOR=#ff0000]{"10891100";"10891200"}[/COLOR][/B],"Functional area",[B][COLOR=#0000ff]{"Z000","Z001","Z002","Z003","Z005","Z006","Z007"}[/COLOR][/B],"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.
Code:
=GETPIVOTDATA("[Measures].[Sum of Amount]",'2017sapbw-data-powerpivot (MTD)'!$B$3,"[2017sapbw].[Profit Center]","[2017sapbw].[Profit Center].&[[B][COLOR=#ff0000]10000100[/COLOR][/B]]","[2017sapbw].[G/L Account]","[2017sapbw].[G/L Account].&[620860]","[2017sapbw].[Functional area]","[2017sapbw].[Functional area].&[[B][COLOR=#0000ff]Z003[/COLOR][/B]]","[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?
