Log In
Register
Home
Active Member
November 5, 2020
Offline
Hi,
I carry a set of dat where I'll have to play around with various data by going through various filters (selections) and arriving at the final root cause. Today I'm using multiple pivots and lot of manual calculation to understand the WoW trend as the selections are a subset of each other. It would be great if I can be assisted either with simple automated reports that populates all the entry per the designed table. And upon just changing my filters in slicers, the rows of my reports also change automatically. This is a case where the filters in slicers also act as the rows in in my report. COUNTIF & SUMF function also didn't work as I'm unable to work with selections within subset (Country -> birth_type -> blood group -> etc). Also since this is a combination of 4 weeks data (3 weeks overlap WoW), it's getting complicated to proceed further. Request you to help me out as this would save 3hours of time per day. Also, please note that the base data is around 11MB.
Excel version: Excel 2016 on Microsoft PC
Awaiting your help at the earliest so that i can put in practice as soon as possible.
Regards,
Indu Goel
Trusted Members
December 20, 2019
Offline
Morning
I am not 100% sure what you are after, but I will make a start working on the summary table in the top right comparing Wk43 with Wk42.
On the Formulas sheet i have recreated the pivot table with formulas, then attached the slicer to the criteria for Gender and Country - not sure what the blood type slicer is for. https://www.myonlinetraininghu.....n-formulas
Is this along the lines of what you were after? as with everything there is a trade-off - pivot tables are quick and easy; formulas take longer to setup and test but can update with changed data.
There is probably a much quicker way to do this with Power query but this could at least prove my understanding
Active Member
November 5, 2020
Offline
Hello,
Thanks for your reply. This works. However having formulae will add load to my sheet. Is there an alternate option please?
Regards,
Indu Goel
Active Member
November 5, 2020
Offline
Hello,
Also I'm unable to reference slicer to the formulae I tried. I tried replicating the formulae sheet you shared, however the slicer isn't getting referenced. Please help.
Regards,
Indu Goel
Active Member
November 5, 2020
Offline
Hi,
Attached is the replicated copy of the data that I'm working on. The blocker is that I'm looking at the values changing in all tables when multiple selections are performed in slicer, however the data changes only in first table. Request you to skim and help me out in the best way possible. Appreciate your earliest assistance.
Usecase:
Selection 1: type-PA
Selection 2: score-high
Selection 3: p type-AAA
Selection 4: provider-B
With the above usecase the value in U18 remains the same pre and pro selection of 3&4. I'm looking for a report where the repots change basis multiple selection in slicer.
This sample report is restricted to just 2 reports (p type & provider), however this will have additional 4-5 reports where we'll have to land to the root by going step by step.
Regards,
Indu Goel
July 16, 2010
Offline
Hi Indu,
You need to right-click the Slicer > Report Connections and then select all the PivotTables in the list that you want that Slicer to filter. Repeat for other Slicers.
In future please start a new thread as this is not a VBA quesiton.
Mynda
1 Guest(s)
