In Power Pivot, I'm trying to drill down from high level category/account data into line item detail. I have this feature set up in Power BI using the Drill Through feature, but due to Read Only limitations in Power BI I'm trying to also implement this feature in Power Pivot.
I have a set of GL accounts and respective variances for actuals to budget/forecast, and my goal is to drill down from these variance totals into the line item detail. This works beautifully in Power BI, however when replicating the setup in Power Pivot, the detail table is missing in the "Drill Down" feature of the Power Pivot pivot table. I can drill down into the control tables containing lists of accounts and cost centers/divisions, however the detail table is missing. Both the variances table and the detail table are linked to the same control tables with relationships. It is not possible as far as i know to build a relationship between the variances table and the detail table because all columns in both tables contain duplicates.
Is this a limitation of Power Pivot? Can you not "Drill Through"/drill across to a different data set? Only Drill Down to control/reference tables?
Edit: Adding the line item detail in the Rows section of the pivot table within the model does not function properly, otherwise I would use that as an alternative to Drilling Down/Drilling Through to jump to the line item detail.
