Calculating (matrix index * a value), then matching to a table based on the description

[06jakegillespie]

I have 3 tables to work with that share a relation to eachother... I will use a dummy data set for data privacy purposes. 

Orders                                                                                                           

 

Ingredient Matrix     

  

 Ingredient List

    

Quantity in Ingredient list will be the (Σ Orders[Order]) * (Index('Ingredient Matrix'[Column1],[Row1]))

For example, say we had a slicer for Order type

our Ingredient List would show the quantities of the formula...

Σ(Σ Orders[Order]) * (Index('Ingredient Matrix'[Column1],[Row1]))

       2 Cake          *          1 Egg = 2 Egg

       2 Cake          *          2 Flour = 4 Flour

       2 Cake          *          3 Vanilla = 6 Vanilla

       2 Cake          *          1 Chocolate = 2 Chocolate

So you could essentially be able to slice by any of the attributes and get a running total of what you need to go and buy.

I was able to do this with excel pretty easily given that it has hlookup, but the lack of ability to transpose search values makes it quite difficult to do.

Similar forum posts revolve around using unpivot query but the formulas in themselves are confusing me... 

I currently use the formula zEgg = SUMX(Orders, RELATED(IngredientMatrix[Egg])) as a measure and it works, but I have to run this calculation for each ingredient. I would have zEgg, zFlour, zVanilla, zChocolate... It gets tedious when I am working with 30+ parts in my real data set. 

Would appreciate any insight, as I am new to DAX.  It seems like it has the same capabilites of Excel, it just requires you to jump  through a lot more hoops. 

[davehus]

Hi @06jakegillespie , If you unpivot the ingredient matrix in powerquery, this will normalize your data and make calculations easier.

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

Hope this helps,

Did I help you today? Please accept my solution and hit the Kudos button.