Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Multiple moving variables in a measure

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?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

 

Let's say these are your tables in Excel:

Untitled.png

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):

Untitled2.png

 

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:

 

Untitled3.png

It should be straightforward from this point to compare the costs and sales price.

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi,

 

Let's say these are your tables in Excel:

Untitled.png

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):

Untitled2.png

 

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:

 

Untitled3.png

It should be straightforward from this point to compare the costs and sales price.

 

Anonymous
Not applicable

Very helpful, I had an intuition that this might be what I needed to do, so thank you for confirming that to me!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.