cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BruceK57 Frequent Visitor
Frequent Visitor

Multiple Scenarios in Power Pivot Forecast

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

1 REPLY 1
Moderator v-sihou-msft
Moderator

Re: Multiple Scenarios in Power Pivot Forecast

@BruceK57

 

There's no directly "what-if" analysis in Power BI. To do "what-if" analysis, you need to create a Parameter table for your forecast calculation. For more details, please see articles below:

 

Parameters for What-If Scenarios in Power BI

WHAT-IF ANALYSIS IN POWER BI

 

You can also vote this idea.

 

Regards,

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 53 members 1,001 guests
Please welcome our newest community members: