Forum Discussion
GETPIVOTDATA Dynamic Referencing from Sliced Pivot Table
Hi all,
I'm using a Sliced Pivot Table to generate dynamic Charts and only realized after working on the rest of my Workbook that changing the reference week will not update the formula, leading to a #REF error
Here is an example of formulas I'm using, with reference week 42, when changing reference week with the slicer, the table will update, but the formula keeps "[NF].[Week].&[42]" returning the error message?
=GETPIVOTDATA("[Measures].[Count of Branch 3]",'PIVOT NF'!$Q$3,"[NF].[Week]","[NF].[Week].&[42]","[NF].[Dep]","[NF].[Dep].&[EXP]")
How can I ensure that the formula updates along with the table? Am I not using GETPIVOTDATA functions correctly?
I mocked-up a simplified model to demonstrate a possible solution.
Include the week number (sliced) in the filter area of the pivot table so that you can reference it in the GETPIVOTDATA function
The key is in the part where you replace .[WEEK].&[42]" by .[WEEK].&[" & C3 & "]"
See attached.
8 Replies
If use CUBE formulae, they could be (same sample as Riny_van_Eekelen suggested)
- for the first selected week
=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Value]", "[FN].[WEEK].[" & CUBERANKEDMEMBER( "ThisWorkbookDataModel", CUBESET("ThisWorkbookDataModel",Slicer_WEEK), 1 ) & "]" )- sum for all weeks selected
=LET( slicer, Slicer_WEEK, connection, "ThisWorkbookDataModel", table, "[FN].[WEEK]", measure, "[Measures].[Total Value]", itemsSelected, CUBESETCOUNT( CUBESET(connection, slicer) ), slicerSelection, CUBERANKEDMEMBER( connection,slicer, SEQUENCE( itemsSelected ) ), REDUCE(0, slicerSelection, LAMBDA(a,v, a + CUBEVALUE(connection, measure, table & ".[" & v & "]")) ) )Did something similar for a friend of mine a few weeks ago
Just put together a version for 2021. Can't test but should do it...
You are variant is more advanced
I mocked-up a simplified model to demonstrate a possible solution.
Include the week number (sliced) in the filter area of the pivot table so that you can reference it in the GETPIVOTDATA function
The key is in the part where you replace .[WEEK].&[42]" by .[WEEK].&[" & C3 & "]"
See attached.
- Wonderful! Exactly what I needed, thank you so much!
