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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi Anonymous ,

      Can't add years to this table.

      Nathaniel