cancel
Showing results for
Did you mean:
Highlighted
cstaulbee Resolver III

## 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

Accepted Solutions Anonymous
Not applicable

## Re: Multiple moving variables in a measure

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.

2 REPLIES 2 Anonymous
Not applicable

## Re: Multiple moving variables in a measure

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.

cstaulbee Resolver III

## Re: Multiple moving variables in a measure

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

Announcements #### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section. #### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products. Top Solution Authors
Top Kudoed Authors
Users online (1,396)