cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
teumic Frequent Visitor
Frequent Visitor

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%

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Nathaniel_C Super Contributor
Super Contributor

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

 

 

Hi @teumic ,

 

 

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

 

Loss Asset.PNG

 

 

 

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

 

 

 

View solution in original post

5 REPLIES 5
Nathaniel_C Super Contributor
Super Contributor

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

Hi @teumic ,

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

 

 

Loss Asset.PNG

 

Highlighted
Nathaniel_C Super Contributor
Super Contributor

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

 

 

Hi @teumic ,

 

 

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

 

Loss Asset.PNG

 

 

 

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

 

 

 

View solution in original post

teumic Frequent Visitor
Frequent Visitor

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

Thanks a lot Nathaniel

 

It seems to work!!

 

Excellent.

 

Michael

teumic Frequent Visitor
Frequent Visitor

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

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 Super Contributor
Super Contributor

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

Hi @teumic ,

sku1.PNG

Can't add years to this table.

Nathaniel

 

sku.PNG

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)