cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cstaulbee Resolver III
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:

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

Re: Multiple moving variables in a measure

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

cstaulbee Resolver III
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!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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