Sum product 2 table filtered on 2 indirect relations

[KamalK]

Hi,

I try to create a measure that multiples the red figures (Sum(Production[Quantity])*Norms[Plan (manhours/piece)]) but they are not directly linked

For each row in Production table, there is only 1 norm 'Plan (manhours/piece)' for that specific product and specific machine. I need to sum the 'Quantity' multiplied by the right norm each time. I try to mix calculate, sumx and cross filtering but can't even get a multiplication into it.

Any idea how to approach this?
.

[v-alq-msft]

Hi, @KamalK 

You may try the following measure to see if it helps.

SUMX(
     ADDCOLUMNS( 
       SUMMARIZE(
               CROSSJOIN(
                  DISTINCT(Products[Product ID]),
                  DISTINCT(Machines[Machine ID])
               ),
               Products[Product ID],
               Machines[Machine ID],
       ),
       "RESULT",
       CALCULATE(SUM(Norms[Plan (Man hours/piece)]))*CALCULATE(SUM(Production[Quantity])),
     )
     [RESULT]
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Try this measure

=Sumx(Summarize(Generate(VALUES(Products[Product ID]),VALUES(Machines[Machine ID])),Products[Product ID],Machines[Machine ID],"Total",Norms[Plan (Man hours/Piece)]*Production[Quantity]),[Total])

Hope this helps.

[mahoneypat]

Hard to know w/o the model to try it on, but I think this measure will work for you.

NewMeasure =
SUMX (
VALUES ( Norms[Product ID] ),
VAR currentproductID =
CALCULATE ( MIN ( Norms[Product ID] ) )
VAR manhours =
CALCULATE (
SUM ( Norms[Plan (Man hours/piece))) Var qty = Calculate(SUM(Production[Quantity] ),
Production[Product ID] = currentproductID
)
RETURN
manhours * qty
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat