Forum Discussion
Multiply multiple rows with a single value in another table
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%
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.
NathanielTotal 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])))
5 Replies
- Nathaniel_C
Community Champion
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.
NathanielTotal 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])))
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
- 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