cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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
1 ACCEPTED SOLUTION
Super User

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

Owen Auger
Blog
6 REPLIES 6
Frequent Visitor

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

Super User

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

Owen Auger
Blog
Super User

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

Owen Auger
Blog
Helper I
Frequent Visitor

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
)```

Community Champion

Just another way of doing it. ....

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

Regards
Zubair