IF and Related DAX or measure

[WalidOthman]

Hi All,

How to add the cost of each product to the Sales table based on the corresponding country ?

Countries 

- South America: Argentina, Brazil,Peru

- Canada

- France

- South Korea 

- Others: rest of the world  (Congo and Nigeria as an example in the Sales table)

Also is this the best way to approach the below by adding a new cost column to the sales table, or is possible to add the cost as a measure ? Eventually I want to calculate the GP (Selling Price-Cost)/(Selling Price)

Pricing Table:

Product Key	Canada Cost	South Korea Cost	France Cost	South America Cost	Others Cost
123	11.5	11.55	11.65	11.2	11
234	11.45	11.5	11.6	11.15	11
345	11.35	11.45	11.55	11.1	11

Sales Table:

Product Key	Country	Selling Price
123	Brazil	12
234	Argentia	12
345	Peru	12
123	Canada	12
234	France	12
345	South Korea	12
123	Nigeria	12
234	Congo	12

[m3tr01d]

To your question "Is it the best way to add a cost column in the sales table", the answer is most likely No.

In term of Data model, it is better for you if you do as @amitchandak said and unpivot the cost but I'm pretty sure you don't need to bring the cost column in the Sales table. You would be better to make a relationship between Sales and the Pricing table based on the product key and country

[amitchandak]

@WalidOthman , Unpivot all the countries in the first table

https://radacad.com/pivot-and-unpivot-with-power-bi
You will country and cost

You can leave other cost asis, if needed

New column in sales 

Cost =

var _1 = maxx(filter(Table1, Table1[Product] = sales[product]  && Table1[Country] = sales[Country] ) Table1[Cost])

var _2 = maxx(filter(Table1, Table1[Product] = sales[product]  && Table1[Country] = sales[Country] ) Table1[Other Cost])

// Var _2 is optional if other cost is a column

//return  _1

//or

return if(isblank(_1), _2, _1 )

[WalidOthman]

@amitchandak Thanks.

Is there a way to do this without unpivtoing the pricing table ?