Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm attempting to write a measure or calculated column that has multiple moving parts and I'm running into some issues try to figure it out. Here's the situation:
I have a table that has prices for material that looks like this:
Material Date Monthly_price Quarterly_price
m1 1/1/2014 $14 $15
m1 2/1/2014 $15 $15
m2 1/1/2014 $7 $9
m2 2/1/2014 $8 $9
The material cost for a product depends on the breakdown of the materials used in the product, and is different for each product. For example, the pricing of the materials for product 1 might be 15% of m1 and 70% of m2. So using the above table, assuming the product is being priced by the monthly column, the materials cost for product 1 in jan of 2014 would be (.15*14) + (.7*7) = 7.
I have no idea how I will go about creating the right calculations to do this because those percentages are not the same for each material for each product. The goal is to be able to show the material costs vs sale price by month. So the cost of materials needs to be calculated for each product by month.
Any clarification you need to understand what I'm trying to say, or any suggestions on how I should organize my data and set up formulas?
Solved! Go to Solution.
Hi,
Let's say these are your tables in Excel:
You could put your products in a table like this Product table above. So product p1 has 15% of m1 and 70% of m2.
Then I'd do it in Power Query. So bring them both into Power Query so it looks like this (I've done it in Power BI):
Then go to the advanced editor on the Product Query and paste this in:
let Source = Excel.Workbook(File.Contents("C:\Test Data.xlsx"), null, true), Product_Table = Source{[Item="Product",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Product_Table,{{"Product", type text}, {"Material", type text}, {"%", type number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Material"},Material,{"Material"},"Material.1",JoinKind.LeftOuter), #"Expanded Material.1" = Table.ExpandTableColumn(#"Merged Queries", "Material.1", {"Date", "Monthly_price", "Quarterly_price"}, {"Date", "Monthly_price", "Quarterly_price"}), #"Inserted Multiplication" = Table.AddColumn(#"Expanded Material.1", "Monthly_cost", each [Monthly_price] * [#"%"], type number), #"Inserted Multiplication1" = Table.AddColumn(#"Inserted Multiplication", "Quarterly_cost", each [#"%"] * [Quarterly_price], type number), #"Grouped Rows" = Table.Group(#"Inserted Multiplication1", {"Product", "Date"}, {{"Monthly Cost", each List.Sum([Monthly_cost]), type number}, {"Quarterly Cost", each List.Sum([Quarterly_cost]), type number}}) in #"Grouped Rows"
Then the Product query should look like this:
It should be straightforward from this point to compare the costs and sales price.
Hi,
Let's say these are your tables in Excel:
You could put your products in a table like this Product table above. So product p1 has 15% of m1 and 70% of m2.
Then I'd do it in Power Query. So bring them both into Power Query so it looks like this (I've done it in Power BI):
Then go to the advanced editor on the Product Query and paste this in:
let Source = Excel.Workbook(File.Contents("C:\Test Data.xlsx"), null, true), Product_Table = Source{[Item="Product",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Product_Table,{{"Product", type text}, {"Material", type text}, {"%", type number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Material"},Material,{"Material"},"Material.1",JoinKind.LeftOuter), #"Expanded Material.1" = Table.ExpandTableColumn(#"Merged Queries", "Material.1", {"Date", "Monthly_price", "Quarterly_price"}, {"Date", "Monthly_price", "Quarterly_price"}), #"Inserted Multiplication" = Table.AddColumn(#"Expanded Material.1", "Monthly_cost", each [Monthly_price] * [#"%"], type number), #"Inserted Multiplication1" = Table.AddColumn(#"Inserted Multiplication", "Quarterly_cost", each [#"%"] * [Quarterly_price], type number), #"Grouped Rows" = Table.Group(#"Inserted Multiplication1", {"Product", "Date"}, {{"Monthly Cost", each List.Sum([Monthly_cost]), type number}, {"Quarterly Cost", each List.Sum([Quarterly_cost]), type number}}) in #"Grouped Rows"
Then the Product query should look like this:
It should be straightforward from this point to compare the costs and sales price.
Very helpful, I had an intuition that this might be what I needed to do, so thank you for confirming that to me!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |