Scenario Analysis - Monthly using nested TREATAS?

DAX DAX Calculations
1

Hi.

I am attempting to do my scenario analysis in a different way. You give a fixed number on the below example, thats fine and makes sense its more technique your trying to show.

I am trying to do my scenario based on a Monthly output.
So I am aware this is where I need to use TREATAS. But how would I nest this 4 times in a sales calc? and all other calcs? If you don’t mind could you please give me some guidance?

This is what i think i need to do:
-Data Model - create a table first of all data I want to run scenario on, (bring in Month and Year though)
-DAX: build core dax calcs to start off with
-DAX:Bring in TREATAS calc -(nested this 4 times) - (This is where i am a little confused)

image
image.jpg1527×786 145 KB

image
image.png1018×318 9.1 KB

Shrik

2

Shrik,

I think this can be best done by using the TREATAS to define the different Measures you want (bottom 4 tables) then to combine them through a SWITCH formula

These are links to both areas to review

Guy

Enterprise%20DNA%20Expert%20-%20Small

3

Hi Guy

Ive just thought probably easier I create each TREASTAS measure separately, right??
TREATAS logic for each of the 4 tables, like below? And then to get the scenario sales (example) probably best to use variable. Anways will give this a try…

Erm using SWITCH not really sure why i would need this in this example?

=CALCULATE(SUM(Sales)
TREATAS(VALUES(Dates[Month),'Pricing Scenarios[Month])


=CALCULATE(SUM(Sales)
TREATAS(VALUES(Dates[Month),'Demand Scenarios [Month])


=CALCULATE(SUM(Sales)
TREATAS(VALUES(Dates[Month),'Cost Scenarios [Month])


=CALCULATE(SUM(Sales)
TREATAS(VALUES(Dates[Month),'Competition Scenarios [Month])
4

Shirk,

I agree with just using the 4 measures by themselves.

The only reason I thought of SWITCH is that you had mentioned nesting them together and SWITCH is just a nested IF.

Guy

Enterprise%20DNA%20Expert%20-%20Small

6

ahh i see
I was going to do it as below.

=CALCULATE(SUM(Sales)
   TREATAS(
      VALUES( TREATAS(VALUES(Dates[Month),'Demand Scenarios [Month]),
        Dates[Month),'Demand Scenarios [Month]))))

But ignore that think my original thought probably overly confuses this.
Probaby best i try this the other way of 4 separate measures!

Cheers mate!
Will let u know how i get on.
Just think this monthly sensitivity of all 4 factors, doing scenario analysis adds in much more value, and more practical if im honest.

Shrik