SOLVED

Get Pivot Data

%3CLINGO-SUB%20id%3D%22lingo-sub-3026648%22%20slang%3D%22en-US%22%3EGet%20Pivot%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3026648%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20i'd%20like%20your%20help%20about%20get%20pivot%20data%20from%20pivot%20model.%20i%20keep%20getting%20%23reff%20results.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mikhailwisnu_1-1638438971519.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331174i78F1D6C9B1AD9EDC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mikhailwisnu_1-1638438971519.png%22%20alt%3D%22mikhailwisnu_1-1638438971519.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20in%20this%20case%20i'd%20like%20to%20show%20map%20chart%20with%20each%20region%20that%20been%20set.%3C%2FP%3E%3CP%3Ethe%20formula%20is%26nbsp%3B%3DGETPIVOTDATA(%22Average%20of%20Diff%20Value%20(%25)%22%3B%24G%2437%3B%22PROVINCE%22%3BJ38).%3C%2FP%3E%3CP%3Ei%20try%20the%20same%20formula%20on%20other%20standar%20pivot%2C%20non%20model%20pivot%2C%20and%20it%20works%20but%20the%20map%20can't%20have%20any%20connection%20slicer%20so%20i%20won't%20use%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mikhailwisnu_2-1638439057922.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331175i8F12C1A8ECBB743C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mikhailwisnu_2-1638439057922.png%22%20alt%3D%22mikhailwisnu_2-1638439057922.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ei've%20tried%20with%20%5B...%5D%20for%20the%20field%3Bitem%20and%20it%20not%20works%20too%20as%20the%20example%20above%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mikhailwisnu_3-1638439212417.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331176i1EAB7C71649312E3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mikhailwisnu_3-1638439212417.png%22%20alt%3D%22mikhailwisnu_3-1638439212417.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eand%20here%20are%20the%20field%20that%20i%20use%20on%20the%20model%20pivot%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20help%20me%20how%20to%20make%20the%20formula%20works%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3026648%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3027663%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20Pivot%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3027663%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1236289%22%20target%3D%22_blank%22%3E%40mikhailwisnu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20empty%20cell%20you%20may%20start%20typing%20%3D%20when%20click%20on%20cell%20within%20PivotTable%2C%20e.g.%20on%20H38%2C%20Enter%2C%20it%20returns%20correct%20formula%20which%20you%20may%20adjust%20with%20parameters.%20Before%20that%20check%20if%20this%20setting%20is%20ON%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20590px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331242iD8ECD3760EC12F99%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI%20guess%20it%20shall%20be%20not%20%22PROVINCE%22%20but%20something%20like%20%22%5BMyTable%5D.%5BProvince%5D%22%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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