Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
@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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |