SumProduct in measure

[BI2018No]

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 formulas in a measure? 

Time	ID	Value
01.01.2018	1	12
02.01.2018	1	14
03.01.2018	1	16
01.01.2018	2	100
02.01.2018	2	200
03.01.2018	2	300

	12*100
+	14*200
+	16*300
=	8800

[Edmundas]

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

[OwenAuger]

@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] )
)

[OwenAuger · Accepted solution]

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

[jordancole0517]

OWEN! THANK YOU!!!!!

This helped solve my post below!

https://community.powerbi.com/t5/Desktop/Row-Subtotals-not-calculating-correctly-for-Forecast/m-p/19...

@OwenAuger 

[BI2018No]

Thanks a lot for a quick and great answer!

One more question if I can allow myself.

If ValueID1 represents and hourly price, and ValueID2 represents products sold, and I want to return an weighted avrerage price, then I'll have to devide the answer here with the SUMX of ValueID2 i guess? How can this be done in a good way?

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
)

[Zubair_Muhammad]

Hi @BI2018No

Just another way of doing it. ....

MEasure =
SUMX ( VALUES ( Table1[Time] ), CALCULATE ( PRODUCT ( Table1[Value] ) ) )