Get Pivot Data

New Contributor

Hello all, i'd like your help about get pivot data from pivot model. i keep getting #reff results.




so in this case i'd like to show map chart with each region that been set.

the formula is =GETPIVOTDATA("Average of Diff Value (%)";$G$37;"PROVINCE";J38).

i try the same formula on other standar pivot, non model pivot, and it works but the map can't have any connection slicer so i won't use it.




i've tried with [...] for the field;item and it not works too as the example above



and here are the field that i use on the model pivot


please help me how to make the formula works

3 Replies
best response confirmed by Hans Vogelaar (MVP)


In the empty cell you may start typing = when click on cell within PivotTable, e.g. on H38, Enter, it returns correct formula which you may adjust with parameters. Before that check if this setting is ON


I guess it shall be not "PROVINCE" but something like "[MyTable].[Province]", etc.

@Sergei Baklan 


Thank you so much for your feedback, and it works!

as you said, at the last formula it must field;item so i rewrite it to:


=GETPIVOTDATA("[Measures].[Average of Diff Value (%)]";pivot_table;"[Table7].[PROVINCE]";"[Table7].[PROVINCE].&[JAKARTA]")


after that i rewrite the province name for each field;item per row and the map chart works fine


Good to know it helped, thank you for the feedback