Filter Costs by Region, or Update Query Data with Proper Costs Column

Hi Everyone,

I could use some help in figuring out how to best find the aggregate costs of goods sold based on region.

Say a good costs $1 in the US, $2 in Europe, and $3 in Asia. Each specific sale is a separate row in my data set, however the cost of the good is not directly mapped to the sale based on region. I have a column that states which sale the region was made, but for the cost, I have separate columns for the $1, $2, and $3(the US Cost column has $1 for every row, Europe shows $2 for every row, etc.) . Is there a way to connect the proper cost for each sale in a single column (i.e. if the sale was in Europe, it will know to make the cost column say $2)? Or, is there an M code to filter it by cost based on region?

For example, I have the costs for each region placed in separate columns (EMEA = EU and APAC = AP - in the region column). Is there a way I can make a new column that assigns the appropriate cost for each row depending on the region, rather than having three separate columns? Or is there an M code that can filter it out at a later stage?

Thanks!