Forum Discussion

Marcos_Noguer's avatar
Marcos_Noguer
New Member
3 years ago
Solved

SUMPRODUCT

Hi all!

 

I need help to set a new measure. I have 3 tables: Product, Product Sales and Product Ingridients. I need to creat a measure that calculates de amount of ingredients I consumed. The tables are related by Product.

 

Example:

 

The correct answer here is 733 * 5 + 719 * 5, no (733+719)*(5+5).

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Marcos_Noguer ,

     

    Here I create a sample to have a test.

    SumProduct =
VAR _SUMMARIZE =
    SUMMARIZE (
        VALUES ( 'Table 1'[ID] ),
        [ID],
        "Value", CALCULATE ( SUM ( 'Table 1'[Value] ) ) * CALCULATE ( SUM ( 'Table 2'[Value] ) )
    )
RETURN
    SUMX ( _SUMMARIZE, [Value] )

    Result is as below.

    Best Regards,
    Rico Zhou

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Marcos_Noguer ,

     

    Here I create a sample to have a test.

    SumProduct =
VAR _SUMMARIZE =
    SUMMARIZE (
        VALUES ( 'Table 1'[ID] ),
        [ID],
        "Value", CALCULATE ( SUM ( 'Table 1'[Value] ) ) * CALCULATE ( SUM ( 'Table 2'[Value] ) )
    )
RETURN
    SUMX ( _SUMMARIZE, [Value] )

    Result is as below.

    Best Regards,
    Rico Zhou

     

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