I'm trying to create a Budget vs Actuals Power Pivot in Excel with a relationship model very similar to the one Bill demonstrated here, however I have my Budget and Actuals values in columns rather than rows and I'm also using Compact rather than Tabular form.
I would like to include a third row containing the Invoice values - which contain text so inserting them as values is not an option - so I can see which Invoices make up the Actuals value. But what I've found is that if a Region/Product combo has a Budget but no Actuals, all the invoices show up.
This is what I currently get:
And this is what I'd like to see:
I've tried several combinations of FILTER and they either don't change anything or they remove the entire Product row because they reduce the Budget to $0. I feel like there should be a way to do what I want here but I have no idea how, and I've been banging my head against it for the last couple of days.
Any help would be greatly appreciated.
I would like to include a third row containing the Invoice values - which contain text so inserting them as values is not an option - so I can see which Invoices make up the Actuals value. But what I've found is that if a Region/Product combo has a Budget but no Actuals, all the invoices show up.
This is what I currently get:
And this is what I'd like to see:
I've tried several combinations of FILTER and they either don't change anything or they remove the entire Product row because they reduce the Budget to $0. I feel like there should be a way to do what I want here but I have no idea how, and I've been banging my head against it for the last couple of days.
Any help would be greatly appreciated.
