Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 b...
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

     

     

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

     

     

     

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

 

 

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's avatar
Anonymous
Not applicable
6 years ago

Thanks a lot Nathaniel

 

It seems to work!!

 

Excellent.

 

Michael