SOLVED

GetPivotData relative referencing

%3CLINGO-SUB%20id%3D%22lingo-sub-2385006%22%20slang%3D%22en-US%22%3EGetPivotData%20relative%20referencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385006%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI'm%20trying%20to%20change%20from%20absolute%20to%20relative%20referencing%20with%20GetPivotData.%3C%2FP%3E%3CP%3EI'm%20not%20an%20Excel%20advanced%20user%20but%20tried%20to%20get%20help%20in%20several%20forums%20and%20did%20not%20succeed%20getting%20an%20answer.%3C%2FP%3E%3CP%3EI'm%20attaching%20an%20example%3C%2FP%3E%3CP%3EPC%2FWindows%2010%3B%20Office%20365%3B%3C%2FP%3E%3CP%3EPower%20Pivot%20model%20using%20Power%20Query%20to%20import%20data%3C%2FP%3E%3CP%3E(sorry%20if%20this%20is%20not%20the%20right%20place%20to%20post)%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2385006%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385352%22%20slang%3D%22en-US%22%3ERe%3A%20GetPivotData%20relative%20referencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1050065%22%20target%3D%22_blank%22%3E%40Miguel_Rolo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCUBEVALUE(%22ThisWorkbookDataModel%22%2C%22%5BOviCamCsts_tabela%5D.%5Brubrica%5D.%5B%22%20%26amp%3B%20G12%20%26amp%3B%20%22%5D%22%2C%22%5BMeasures%5D.%5BSum%20of%20IVA%5D%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388361%22%20slang%3D%22en-US%22%3ERe%3A%20GetPivotData%20relative%20referencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreat%3C%2FP%3E%3CP%3EIt%20works%2C%20problem%20solved%20(I%20need%20to%20study%20cube%20functions%26nbsp%3B%20.%20.%20.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388831%22%20slang%3D%22en-US%22%3ERe%3A%20GetPivotData%20relative%20referencing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1050065%22%20target%3D%22_blank%22%3E%40Miguel_Rolo%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%3C%2FP%3E%0A%3CP%3EJust%20in%20case%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.excelcampus.com%2Fcubevalue-formulas%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ETips%20%26amp%3B%20Tricks%20for%20Writing%20CUBEVALUE%20Formulas%20-%20Excel%20Campus%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello

I'm trying to change from absolute to relative referencing with GetPivotData.

I'm not an Excel advanced user but tried to get help in several forums and did not succeed getting an answer.

I'm attaching an example

PC/Windows 10; Office 365;

Power Pivot model using Power Query to import data

(sorry if this is not the right place to post)

Thanks

 

5 Replies
best response confirmed by Miguel_Rolo (New Contributor)
Solution

@Miguel_Rolo 

I'd use

=CUBEVALUE("ThisWorkbookDataModel","[OviCamCsts_tabela].[rubrica].[" & G12 & "]","[Measures].[Sum of IVA]")

Please check attached.

@Sergei Baklan 

 

Great

It works, problem solved (I need to study cube functions  . . .)

 

Thanks again

@Sergei Baklan 

Thank you for the link, very useful.

When starting using the cubefunctions I tried to use GetPivotData using in the cell reference the syntax like in your example [" & A1 &"] and it works!

I hope it is possible to understand what I mean

(sorry for my late reply)

Thanks

@Miguel_Rolo 

Yes, cube functions are working with text parameters. If you combine the text properly any function shall work with it.