Hi everyone,
My team has requested for another level of detail in our dashboard- they want to be able to see the data points behind the numbers.
For example, if the data shows 3 employees in Hong Kong, they will click on the number '3', and the names of the 3 employees will show up. It's an easy enough pivot table drill down.
The challenge is that I have a number of tables linked together for this data, and I also have a few calculated columns. I am teaching myself Powerpivot and the GETPIVOTDATA syntax to retrieve those details.
My solution is to have a dozen pivot tables in one file (using Power Pivot to combine multiple source files) and slicers linked to all of them. The pivot tables can be copied to 1 tab for easy use, and the slicers will be placed in that tab. Then, I want to write macros to drill down the data.
My questions:
1. Is this the best solution to this problem? I have files for each month of the year, with thousands of employee records, and all of them need to be in the same pivots, for us to get the KPIs we want.
2. I am using PowerPivot, multiple pivot tables, and macros in the same file. It feels like climbing up the walls of a 50-storey sky scraper - the whole set up could come crashing down any minute.
Suggestions? Ideas? How would you approach this challenge?
3. Finally, for my macros, I have a macro that can do the drill down, but I don't know how to point it to the Active Cell I want. In my ideal world, the macro would read the pivot table and slicer settings, and do the drill down.
My sample file has just some basic info, and a model of how this would work (post the PowerPivot stage). All sheets except the 'Snapshot' sheet will be hidden. Please let me know how I can make this simpler, and how to write the macro, if it is still required. Thanks!
My team has requested for another level of detail in our dashboard- they want to be able to see the data points behind the numbers.
For example, if the data shows 3 employees in Hong Kong, they will click on the number '3', and the names of the 3 employees will show up. It's an easy enough pivot table drill down.
The challenge is that I have a number of tables linked together for this data, and I also have a few calculated columns. I am teaching myself Powerpivot and the GETPIVOTDATA syntax to retrieve those details.
My solution is to have a dozen pivot tables in one file (using Power Pivot to combine multiple source files) and slicers linked to all of them. The pivot tables can be copied to 1 tab for easy use, and the slicers will be placed in that tab. Then, I want to write macros to drill down the data.
My questions:
1. Is this the best solution to this problem? I have files for each month of the year, with thousands of employee records, and all of them need to be in the same pivots, for us to get the KPIs we want.
2. I am using PowerPivot, multiple pivot tables, and macros in the same file. It feels like climbing up the walls of a 50-storey sky scraper - the whole set up could come crashing down any minute.
Suggestions? Ideas? How would you approach this challenge?
3. Finally, for my macros, I have a macro that can do the drill down, but I don't know how to point it to the Active Cell I want. In my ideal world, the macro would read the pivot table and slicer settings, and do the drill down.
My sample file has just some basic info, and a model of how this would work (post the PowerPivot stage). All sheets except the 'Snapshot' sheet will be hidden. Please let me know how I can make this simpler, and how to write the macro, if it is still required. Thanks!
