Have 2 fact tables fActual and fBudget. Have 1 measure on each fact table sum(Table[Amount]) to calculate total Actual and Total Budget. Have added a third measure on the fActual table... Act Vs Bgt = [Total Actual] - [Total Budget]. Have added a pivot table with dimension from dCalendar table (month Column) in the row quad of the PVT and 3 measures in the 4th quad - Total Actual, Total Budget and Act Vs Bgt.
My question relates to drilldown functionality. I can drilldown to the detail on Actual for month, on budget for the month however when I try to drill down on Avt Vs Bgt Diff I get the Actual numbers again (I am guessing because the measure is sitting on the fActual Table). Is there a way around this or am I expecting too much from power pivot?
Apologies due to the sensitivity of the data it is difficult to share the Data Model
My question relates to drilldown functionality. I can drilldown to the detail on Actual for month, on budget for the month however when I try to drill down on Avt Vs Bgt Diff I get the Actual numbers again (I am guessing because the measure is sitting on the fActual Table). Is there a way around this or am I expecting too much from power pivot?
Apologies due to the sensitivity of the data it is difficult to share the Data Model