Help with calculated column based on two other columns

[dkoepke]

Hi all,

Need some help calculating a column based on the contents of two other fields.

The data I have is historical sales, but also mixed with budget data

So for each product I have a business unit and actual sales one line then same for budget in the same table.

I need to create a calculation of sales (Converted for currency) based on if it is from a certain business unit.  So for USA it would conver to ￥ from USD, but only for actual sales transation lines excluding budget lines.  But I also need to convert about 6 different currencies based on the content of a business unit field.

Hope this makes sense.  

Thanks,
David

[Pragati11]

Hi @dkoepke ,

Can you please post some sample data along with the required output? It's will make easier to answer your query.

Thanks,

Pragati

[dkoepke]

Data Type	Year Month	BU	Qty	SalesAmt Local Currency	Model
MonthlyReport	2019/12	DUS	3	50673	52D
MonthlyReport	2019/12	DUS	1	17179.5	52D
MonthlyReport	2019/12	DUS	41	678661.05	52D
Budget	2019/12	DUS	4	55600	52D
Budget	2019/12	DUS	3	45700	53D
Budget	2019/12	DUS	40	62600	52D
MonthlyReport	2019/12	DAS	2	30000	52D
MonthlyReport	2019/12	DAS	4	40000	52D
MonthlyReport	2019/12	DAS	5	42000	52D
Budget	2019/12	DAS	1	4500	53D
Budget	2019/12	DAS	1	4500	53D
Budget	2019/12	DAS	1	4500	53D