Forum Discussion

Molin's avatar
Molin
Helper I
5 years ago

Multiplying values from different tabel

Hi, 

I am working on a report where I want to calculate theoretical usage for different ingredients used in production, based on sales numbers. 

Based on the model below, I have tried the following measure: 
" Theoretical Usage =

Sumx(values(MenuComponent[MenuItem]),Sum(IngredientComponent[Amount])*sum(CheckDetails[SalesCount])) " 
 
The formula works great when I filter down on a single MenuItem, however when there are two or more MenuItems, the value is far to high. In excel I would use a Sumif or Lookup function and I have previously used Related in Power BI, but thats donts seem possible in this case. Is there a way to get the total usage for each ingredients based on all sales count?

Thanks in advance.  
 




Need Help
Show and Tell
Tips and Tricks

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    Molin I'm not sure what you're asking, can you provide sample numbers of expected values and what the SUMX is giving? Your measure looks to be on the right track, not sure if you are looking more for an AVERAGEX?

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Molin , Try like

    Sumx(values(MenuComponent[MenuItem]),Max(IngredientComponent[Amount])*sum(CheckDetails[SalesCount]))