Relative references and GETPIVOTDATA from POWERQUERY

[nekthen]

=GETPIVOTDATA("[Measures].[Sum of Net amount]",'Sheet1'!$A$3,"[SAGE_Sales detail_0].[Date (Year)]","[SAGE_Sales detail_0].[Date (Year)].&[2023]","[SAGE_Sales detail_0].[Account Reference]","[SAGE_Sales detail_0].[Account Reference].&[ACCREF1]")

So this is my Generated formula. I need to change ACCREF1 to be a relative reference to the list of accounts in column A, but

=GETPIVOTDATA("[Measures].[Sum of Net amount]",'Sheet1'!$A$3,"[SAGE_Sales detail_0].[Date (Year)]","[SAGE_Sales detail_0].[Date (Year)].&[2023]","[SAGE_Sales detail_0].[Account Reference]",A2) returns #REF!

Surely I am missing something simple but what is it?

 

[nekthen]

This formula works fine =IFERROR(GETPIVOTDATA("Value",Pivot!$A$3,"Account",$C37, "Month",TEXT(T$6,"mmm")),0), but this pivot table does not use a table in the data model. So the question is how to alter the formula.
I tried using this format with Generate GETPIVOTDATA turned off on the pivot table linked to the data model but all I get is the dreaded #REF!

 

[RoryA]

Assuming A2 contains the text ACCREF1, then you want this:

=GETPIVOTDATA("[Measures].[Sum of Net amount]",'Sheet1'!$A$3,"[SAGE_Sales detail_0].[Date (Year)]","[SAGE_Sales detail_0].[Date (Year)].&[2023]","[SAGE_Sales detail_0].[Account Reference]","[SAGE_Sales detail_0].[Account Reference].&["&A2&"]")

 

[RoryA]

Glad we could help, and welcome to the forum, by the way!

 

[nekthen]

Glad we could help, and welcome to the forum, by the way!

Thanks a lot.