Results 1 to 2 of 2

Thread: GETPIVOTDATA formula with multiple criteria

  1. #1

    GETPIVOTDATA formula with multiple criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have the following Array GETPIVOTDATA function that takes multiple criteria from a regular pivot table.

    Code:
    {=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.

    Code:
    =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?

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    Please note this forum's stance on cross-posting and amend your opening post accordingly: http://www.excelguru.ca/content.php?184
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •