Displaying upgrade costs based on multiple factors

[QLD_Jay]

Hi all,

I'm struggling with something that I feel should be fairly straight forward and am seeking some advice.

I'm currently working on a dashboard to assist with the decision making behind the upgrade of devices across multiple locations and the associated costs.

I have 3 tables (amongst others) containing,

1. locations and their Devices (some to be upgraded some not)

2. locations that have a Module (some locations may need their device upgraded but can carry on using the existing Module)

3. the $ value of the New Device and Module (as a static table of values) 

So something like (please excuse my crappy html tables),

Devices

Location	Device	Upgrade
Loc1	t800	1
Loc2	t800	1
Loc3	t1000	0

With a 1..1 relationship with

Modules

Location	Module
Loc1	Model1
Loc3	Model1

Upgrade costs

New Device	Cost	GST	Cost incl GST
T1000	500	50	550
Model1	100	10	110

My aim is to chuck a matrix or table on a page that will display the number of devices that need to be upgraded and total costs based on other parameters the user has set eg. limiting the range of locations being looked at. The costs need to take account of those locations where a new device is required but a Module is not.

Should I create a calculated column in Devices that checks to see if a location is also in Modules? Maybe a calculated column in Devices that's filled with the cost from Upgrade Costs if Upgrade evaluates to true?

I'm really struggling to produce something functional and would love a bit of advice here. All suggestions welcome!!!

[QLD_Jay · Accepted solution]

After some screwing around I ended up creating two calculated columns in Devices that returned 0 or 1 based whether or not the device needed an upgrade and had a Module. Then I created a column with a nested if statement that looks something like this.

UpgradeCost = IF( AND(Devices[UpgradeReqd]=1, Devices[HasModule]=0 ), 660,

    IF( AND(Devices[UpgradeReqd] = 1, Devices[HasModule] = 1 ), 550,

        IF( AND(Devices[UpgradeReqd] = 0, Devices[HasModule]=0 ), 110, 0

        )

    )

)

Eliminates the need for the Upgrade Costs table but now everything is hard coded.

Fortunately the numbers won't change for sometime.