Okay so little strange request here so let me explain carefully. I am building out a new report for company wide use by many people who are not so great at excel lets say. I am planning to build dashboards of basically pivot tables that can be controlled through slicers. The data is from a few simple tables and I was planning on automating (since daily report) through powerquery/powerpivot to make a quick relational database. The problem is that not only do I want the user to be able to filter the data through a slicer (like normal) but I want them to be able to filter what fields are shown the rows. I don't have a good solution for this. The only one I could find is where the button controls whether or not the field is in the pivot table through VBA. Problem with this is that a) not sure it would work with powerpivot and more importantly b) these users don't know excel so asking them all (lots of users) to have VBA enabled and make sure to have the security thing hit correctly would be a huge pain. Anyone have a solution for my dashboard? I put example of fields and what I am looking for below in case my explaining was poor.
Example: Lets say you have orders from different locations in an order table. There are several columns in your data including Country, Region, State, City, Zip Code, Address that you want the user to be able to see in the rows of your pivot table but also filter on it. You want the user not only to be able to filter the pivot table on a particular state but also use buttons or slicers to see only the columns (in the rows of pivot table) that they want to see. Meaning I might want to only see by Region and State or by State and City in the rows of the pivot table. The users don't know how to make these changes in the pivot table options and so you need a slicer or other solution for them to easily adjust.
Example: Lets say you have orders from different locations in an order table. There are several columns in your data including Country, Region, State, City, Zip Code, Address that you want the user to be able to see in the rows of your pivot table but also filter on it. You want the user not only to be able to filter the pivot table on a particular state but also use buttons or slicers to see only the columns (in the rows of pivot table) that they want to see. Meaning I might want to only see by Region and State or by State and City in the rows of the pivot table. The users don't know how to make these changes in the pivot table options and so you need a slicer or other solution for them to easily adjust.
