I am building an Income Statement using Power Pivot. I want to give the user the option to do "what-if" analysis on the fly. Example: What if sales increase 10%? Decrease 5%?
In my Data Model I have an unlinked table with the different scenarios, and I have put a slicer on the Pivot Table that references the measure from the unlinked table. Everything works well: You click 5% on the slicer, all values increase 5%.
Here is where I am having a problem. I want the user to be able to pick a different scenario for each of three Income Statement categories: Income, Cost of Goods Sold and G&A Expense. I cannot share a sample file, but I can explain the setup.
There are 4 tables in the data model:
1. Income Statement Values, which list the income and expense values by category.
2. Sales Forecast, which has the Revenue scenarios.
3. COGS Forecast, which has the Cost of Goods Sold scenarios.
4. GA Forecast, which has the G&A Expense scenarios.
The scenarios for items 2, 3 and 4 are all the same: -5%, 0%, +5%,, + 10%.
In the Income Statement Values table, there is a column labeled Header, which I use to control the order of how items appear in the Income Statement pivot table. In the Header Column are the labels Revenue, COGS and G&A.
Here's specifically what I would like to have happen:
1. 3 Slicers on the Pivot table:
a) Revenue scenarios
b) COGS scenarios
c) G&A scenarios
2) If the user decides Revenues go up 10%, COGS goes up 5% and G&A doesn't change, this would be the formula:
IF(Header="Revenue",Value x (1+Revenue Scenario Selection),IF(Header="COGS",Value x (1+ COGS Scenario Selection), IF(Header="G&A",Value x (1+G&A Scenario Selection),Value))))
I can't figure out how to do this in DAX. Any ideas?
Thank you,
Bruce
