SOLVED

Get Pivot Data

Copper Contributor

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

 

mikhailwisnu_1-1638438971519.png

 

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.

 

 

mikhailwisnu_2-1638439057922.png

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

 

mikhailwisnu_3-1638439212417.png

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)
Solution

@mikhailwisnu 

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

image.png

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

@mikhailwisnu 

Good to know it helped, thank you for the feedback

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@mikhailwisnu 

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

image.png

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

View solution in original post