Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Nathaniel_C
Super User
Super User

 

 

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

 

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

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Nathaniel_C
Super User
Super User

 

 

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

 

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

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

Hi @Anonymous ,

sku1.PNG

Can't add years to this table.

Nathaniel

 

sku.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thanks a lot Nathaniel

 

It seems to work!!

 

Excellent.

 

Michael

Nathaniel_C
Super User
Super User

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

 

 

Loss Asset.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors