Forum Discussion

BI2018No's avatar
BI2018No
Frequent Visitor
8 years ago
Solved

SumProduct in measure

Hello technical experts!   I would like to perform a "sumproduct" on two time series in the same datatable. All ID1 should be multiplied by ID2 for all time slots. How can I do this using DAX formu...
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi there,

     

    With your existing table, a measure like this using SUMX will give you the result you're looking for. I've used variables to make the calculation clearer.

    SumProduct measure = 
SUMX (
    VALUES ( YourTable[Time] ),
    VAR ValueID1 =
        CALCULATE ( SUM ( YourTable[Value] ), YourTable[ID] = 1 )
    VAR ValueID2 =
        CALCULATE ( SUM ( YourTable[Value] ), YourTable[ID] = 2 )
    RETURN
        ValueID1 * ValueID2
)

    Regards

    Owen

Edmundas's avatar
Edmundas
Frequent Visitor
3 years ago

Hi, could you please help me write sumproduct in DAX as it is shown in example (column "Result')? Thanks a lot

 

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
3 years ago

Edmundas 

You could also create this column further upstream (e.g. Power Query).

 

However, below is an example of how to calculate with DAX (PBIX attached).

 

Since your Excel formula uses a combination of SUMPRODUCTs to calculate a conditional sum, you can replicate the behaviour with a calculated column like this:

Result = 
CALCULATE (
    SUM ( Data[Ratio] ),
    ALLEXCEPT ( Data, Data[Date], Data[Region] )
)