Multiply multiple rows with a single value in another table

[Anonymous]

All

I'm trying to calculate a yield. Take the following example. I have a table where I have sales data per SKU per month (Table_sales). Several SKU's are linked to a single asset. The relation between the asset and SKU's in maintained in a seperate table (Table_Asset). Next to this I have a table that contains some key metrics on the assets like a loss percentage (Table_Asset_Metric). Table_Asset and Table_Asset_Metric are linked to eachother.

I'm trying to calculate to calculate the amount of assets I'm losing based on the sales. 

Here an example.

SKU A 2000 sold in month 1 --> SKU A is shipped in Asset XYZ

SKU A 3000 sold in month 2 --> SKU A is shipped in Asset XYZ

SKU B 1000 sold in month 1 --> SKU B is shipped in Asset XYZ

SKU B 2000 sold in month 2 --> SKU B is shipped in Asset XYZ

Per shipment of Asset XYZ I have loss of 2%.

I would like to calculate the total amount of asset XYZ I'm losing. In excel I would calculate like this:

Loss asset XYZ = ( total volume SKU A + total volume SKU B) * Asset loss per shipment = (2000 + 3000 + 1000+ 2000) * 2% = 160

Table sales: 

SKU Month  volume     
A        1         2000          
A        2         3000         
B        1         1000             
B        2         2000  

C        1           500

C        2         1000

Table_Asset

SKU      Asset

A            XYZ

B            XYZ

C            123

Table_Asset_Metric

Asset         Losses%

XYZ              2%

123              5%

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

Here is the PBIX  SKU

This was pretty straightforward with your good explanation. Thank you. However when I added the filters, I realized that one would need a weighted average loss dollar amount if there were more than 1 loss percentage. (i.e. select A and C ) So that was interesting.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Total Sales for Selected SKU = CALCULATE(SUM(Sales[Volume]),ALLSELECTED(Sales[SKU]))

sumx(sales,Sales[Volume] * [Losses per Asset Carrier All Selected])

Percentage Loss = Divide([Total Losses],[Total Sales for Selected SKU])


Losses per Asset Carrier All Selected = CALCULATE(AVERAGE(ASSET_METRIC[Losses%]),Filter(Sales,Sales[SKU]=(Sales[SKU])))

[Anonymous]

hi Nathaniel

I got an additional challenge. 😉 Assume that I add in the example above a column to the table with the losses. I would the column year. In this case I would insert a loss % value for 2018 and another row with a value for 2019. How should I calculate in this case the same but taking e.g. the loss for 2019 only.

[Nathaniel_C]

Hi @Anonymous ,

Can't add years to this table.

Nathaniel

[Anonymous]

Thanks a lot Nathaniel

It seems to work!!

Excellent.

Michael

[Nathaniel_C]

Hi @Anonymous ,

Is this what you are looking for in your question?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel