Pivot Table/Power View > Drill Down Table > Column selection + Formatting > How to achieve?

[vinaypugalia]

Hello,

I am new to Pivot Table/Power View and have got a task in hand.

I am supposed to prepare "Drill Down" report either using Pivot Table (preferable) or Power View.

I have read on this and have learned that this could be achieved by creating Hierarchies either in the Model itself or ad-hoc and once the columns are selected under Rows/Values, drill down will be taken care of by Excel itself.

However, what I have not understood is, what level of control do I have over the level 2 or level 3 tables/charts. Is there any way by which I can change the columns to be shown in Level 1, Level 2 or Level 3 tables when the user drills down from level 1. Also, am I allowed to format those tables while designing the report?

Can some one please share their views on this and guide me further?

Thanks!

[leonardmurphy · Accepted solution]

Are you using Excel or the Power BI Desktop tool or PowerBI.com for your task? (Without knowing, I can only be general)

Generally, you don't have dynamic control over level 2 or level 3 columns. A pivot table has levels that are pre-defined and the user can drill down to the lowest level you've put on the pivot table.

The two options:

1. If the user feels comfortable manipulating the pivot table/editing the report, they can add in the columns they want and change them at will. This isn't that complicated and it's well worth an end-user learning how to do this. (Especially if the data source is a cube). It empowers them to explore the data vs. just looking at it.

2. If the user really isn't comfortable changing the pivot table themselves, creating multiple pivot tables with the different drill down options they would like.