Filtering Non Pivot column based on Pivot Slicer
Hi
I have to urgently fix my report for this problem. Please help! :(
My report is as attached. My problem is that the WoW variance is calculated based on my two different pivot tables.
If I filter my Pivot1 my Pivot 2 is also filtered because of my slicer settings. Because I'm then hiding my Pivot2 which has previous week data which I use to calculate week on week variance.
The moment I filter my pivot based on the Slicers - Eg: Webchat, my results are all correct except that the WoW Variance column will display #REF for Calls and Emails and my WoW variance willl be showing all Calls,Emails and Webchat but I want it to display ONLY the "Webchat" as this is what is pivots are sliced on!! Please refer my attached excel for further illustrations.
| Attachment | Size |
|---|---|
| Slicer check.xlsx | 22.56 KB |
add the calculated data to
add the calculated data to the source data, and create a pivot table off the whole data
... or add a calculated field to your pivot table in Excel
in ribbon:
Pivot table tools => Options => formulas => calculated field
I cant reference data from another pivot
Thanks much for the reply.
Calculated field is not allowing me to refer data from another pivot to existing pivot. As Im using getPivotdata formulas in the calculation : Eg: GETPIVOTDATA("TA Calls ",$A$21,"Site","Dublin")-GETPIVOTDATA("TA Calls ",$U$21,"Subs Week And Year (1)","201433","Site","Dublin")
This is what gives me my WoW variance. and the Subs Week and year refers to current week and previous week data which is held in two different pivots.
why do u need 2 pivots
why do u need 2 pivots ?
create a new column in your data with the calculation you need..
data complicated
the data is too complex to look at current week and previous week for WeekOnWeev variance calculation. THis is a daily report and as the days add on I must compare like with like - eg: If my current week has data only for Monday and Tuesday then my Previous Week should also be only Monday and Tuesday.
and if any slicer filter is applied it should then dynamically change the variance based on the slicer. Which happens as I have the slicers from Pivot1 with current week connected to Pivot2 with previous week and then flag to ensure that my previous week always has data for the same days as the current week etc : Mon,Tue etc.
So Im using the above formula from two pivots to look at the variance.