topic Multiply multiple rows with a single value in another table in DAX Commands and Tips

Multiply multiple rows with a single value in another table

Anonymous — Thu, 26 Sep 2019 14:55:34 GMT

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%

Re: Multiply multiple rows with a single value in another table

Nathaniel_C — Fri, 27 Sep 2019 13:27:08 GMT

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

Re: Multiply multiple rows with a single value in another table

Nathaniel_C — Fri, 27 Sep 2019 15:19:55 GMT

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

Re: Multiply multiple rows with a single value in another table

Anonymous — Fri, 04 Oct 2019 17:30:45 GMT

Thanks a lot Nathaniel

It seems to work!!

Excellent.

Michael

Re: Multiply multiple rows with a single value in another table

Anonymous — Mon, 07 Oct 2019 07:33:00 GMT

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.

Re: Multiply multiple rows with a single value in another table

Nathaniel_C — Mon, 07 Oct 2019 22:17:37 GMT

Hi Anonymous ,

Can't add years to this table.

Nathaniel