Scenario Analysis with Alternate States

[#1]

All - I am looking to to compare alternative states and use a slider to shift # of Hours from company A to company B to generate $ savings. Essentially it would be:

# of Hours(Firm A) * vVar (slider) * (Firm A Rate - Firm B Rate)

Correct?

Right now I have the following equation(s):

State 1 - [Current Firm]

State 2 - [New Firm]

# of hours Firm A  = num(sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"}, Position -= {"=NULL()"}>} [Submitted Hours]),'#,##0')

Avg Rate $  Firm A -

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

/

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours]),'$#,##0'

Avg Rate $  Firm B -

sum({[New Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

/

sum({[New Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours]),'$#,##0'

Any idea how to put this all into on equation where a slider (vVar) will effect the amount saved from shifting hours from firm A to firm B

[sunny_talwar]

Not sure I completely understand your requirement. Would you be able to elaborate a little more?

[#3]

Scenario -

Select a firm in State 1:

Name:Firm A

Hours: 1,502

Avg Rate: $365

Select a firm in State 2:

Name: Firm B

Hours: 500

Avg Rate: $225

Use slider to shift to 500 hours @ $365 rate to Firm B where the rate is $225. What's your total savings?

Difference of $365 - $225 *500 hours = savings

[swuehl]

Seems like you already have your parts of the expression working on their own, right? What's the exact issue when combining the expressions, then? Have you tried:

=(

(

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

/

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours])

)

-

(

sum({[New Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

/

sum({[New Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours])

)

)

* vVar

[#5]

The expressions with Sum({[Current Firm}]), Sum({[New Firm]} seem to not be working on their own as well..

[swuehl]

Well, then I would suggest to fix these expressions first.

Do the expressions work in default state when selecting a firm?

I think this set modifier will not work as expected, since NULL() is not a values, hence can't be removed assigned list of values of field Position:

,Position -= {"=NULL()"}

You might want to look into

Excluding values in Set Analysis

edit:

Or maybe try

,Position = {"*"}

instead.

[#7]

They do not work in the default state when a firm is selected, only their corresponding state within the set analysis. Looking into it now, but is the syntax correct for the alternate state's in the set analysis e.g.

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Amount Spend])

/

sum({[Current Firm]}{<[Vendor Type] = {'Consulting'},[Submitted Hours] -= {'0'}, [Submitted Rate] = {"<2000"} ,Position -= {"=NULL()"}>} [Submitted Hours]),'$#,##0'

[sunny_talwar · Accepted solution]

May be this:

Sum({[Current Firm]<[Vendor Ty

[#9]

Ahh yes, that was the issue! Thanks Sunny.