Calculation Using 2 Data Sources

[brownsamuelb]

I am trying to calculate a list of metrics associated with a group of properties.

In one source spreadsheet I have the list of properties and their associated financials (ill call this source1).

Property	Revenue	Expenses	Rooms
ABC	100,999	55,888	223
XYZ	78,000	70,111	130

In another spreadsheet I have a list of the metrics and how it should be calculated (ill call this source2).

Rule	Calculation Logic
Profit	Revenue - Expenses
Expense per room	Expenses/Rooms

What would be the best method to say "For each property in source1, I want every metric in source 2 to be calculated". Keep in mind there is the potential that the property list will grow and I would like to avoid having to reconfigure the model every time a property gets added. 

'-------------------------------------------------------------------------------------------------------------------------------------------------

Coming from an Excel background, I would have created another spreadsheet with all of the metrics names along the vertical axis and property names across the horizontal axis. I would then use an Index-Match-Match formula to pull in the financials associated with a property in the metric calculation format (see below). Im just not sure if this can be done in Alteryx nonetheless if it is the most efficient method of achieving my goal.

Property ► Metric ▼	XYZ	ABC
Profit	=INDEX(propertyTable,MATCH("XYZ",propertyTableRows,0),MATCH("Revenue",propertyTableHeaders,0)- INDEX(propertyTable,MATCH("XYZ",propertyTableRows,0),MATCH("Expense",propertyTableHeaders,0))	=INDEX(propertyTable,MATCH("ABC",propertyTableRows,0),MATCH("Revenue",propertyTableHeaders,0)-INDEX(propertyTable,MATCH("ABC",propertyTableRows,0),MATCH("Expense",propertyTableHeaders,0))
Expense per Room	=INDEX(propertyTable,MATCH("XYZ",propertyTableRows,0),MATCH("Expense",propertyTableHeaders,0)/ INDEX(propertyTable,MATCH("XYZ",propertyTableRows,0),MATCH("Rooms",propertyTableHeaders,0))	=INDEX(propertyTable,MATCH("ABC",propertyTableRows,0),MATCH("Expense",propertyTableHeaders,0)/ INDEX(propertyTable,MATCH("ABC",propertyTableRows,0),MATCH("Rooms",propertyTableHeaders,0))

Any feedback or help is greatly appreciated.

SB

[fmvizcaino]

Hi @brownsamuelb ,

There is not an easier way of doing but it is perfectly possible. But before that, I would like to ask if it isn't a option to insert every calculation inside a formula tool.

Best,

Fernando V.

[RolandSchubert · Accepted solution]

Hi @brownsamuelb ,

I think, the solution would be to use the Dynamic Formula Tool, one of the CReWMacros. It would require one small modification to spreadsheet 2 (add data type and size):

You could then feed spreadheet 1 and spreadsheet 2 into the tool and add properties to sheet 1 and calculations to sheet  2:

This solution would stay with the data sheet - formula sheet concept you are using today, but I think it's even simpler compared to your current Excel solution ...

I've attached a sample workflow. Hope this is helpful.

Regards

Roland