Dec 02 2021 02:02 AM
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 :)
Dec 02 2021 07:09 AM
SolutionIn 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.
Dec 09 2021 04:17 PM
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
Dec 10 2021 06:21 AM
Good to know it helped, thank you for the feedback
Dec 02 2021 07:09 AM
SolutionIn 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.