Selecting Drill Down Columns

I'm creating monthly reports using a variety of queries and I'm trying to understand the logic behind the drill down feature. I'm using RELATED to bring in all needed information into my main table, but the drill down is only displaying certain columns from that table. In my case, it is showing some columns I used for calculations, but leaving out some of the most important fields.

I have not found an intuitive way to control which of the fields are displayed in the drill down details. Is there any way to control which columns show up for the detail?

Thank you!

Are you talking about when you double-click in the pivot table and create a new sheet that contains the underlying records? If so, the table your measure/calculated column is assigned to makes a difference on which columns will show on your new sheet, even though the calculation should be the same regardless of the table assigned. This is why I'm not a fan of creating a separate table just to hold measures. It really messes with the drill-down feature.

If you're talking about the quick explore option (the magnifying glass with the lightning bolt), I'm not sure why you don't see all of your columns. In the test I tried, the column that started out in the table from the beginning was selected by default, but I can expand the table name to drill to any of my other columns, including the ones I bring in with the RELATED formula.

Thank you for your response. I'm talking about when I double click on a particular value in a Pivot Table using data from a Power Pivot data model to bring up the details. In my original table, I have info like Entry #, Account #, Description, Department, Vendor, etc. I created a few formulas to fix blank entries and then used RELATED to bring in a few other columns of relevant information. These calculated fields and measures are all in my original table.

Now that I have those columns in my data table, when I double click to drill down, it shows some irrelevant fields (my working calculation columns) and leaves out some of the most important (Entry #). I'm looking for a way to control which of the columns from my data model show up in details sheet. Do you have any recommendations to prioritize which columns show up in the details?

I think I figured it out, but I don't think you'll like it. Here are my observations from tinkering around in a very small model:

1. If you double-click into a metric assigned to your lookup table, it will show all details/columns from the relevant rows in that table. It's probably assumed that since it's a lookup table, there's only one record per unique item or key.
2. If you double-click into a metric assigned to your data table (where you are using related to pull in additional fields), it will only show metrics and calculated columns for the relevant rows. The assumption is that there are multiple rows/transactions and you'd want to summarize this data. This still pulls in your RELATED columns because they are a calculation and not static data.

The workaround then for forcing a column to show in the drill-down sheet from a data table measure is to duplicate the column in the table. If you really want to show EntryID, you'll need a new column like EntryIDCalculated where the formula is just =[EntryID]. You could even hide that column from the client tools so it doesn't show in the pivot table fields, and it will still show in the drill-down. If you knew you were doing that setup from the beginning, you could give the original name something like EntryIDOriginal, hide that one, and give the new calculated column a user-friendly name of EntryID. Then the same column name would show in the pivot table fields and the drill-down sheets.

More info

Unfortunately, there's no option to select which fields show in the drill-down, at least not that I can find. I've never had the impression that there's much love for the feature for development, even though it tends to be one the most exciting features to users in my experience.

Thank you for your help! While not an ideal solution, I can at least use that as a patch for the time being. I'll us the reference formula to make sure that my desired columns show up. Any suggestions on how to hide unwanted columns from the detail drill down output?