Forum Discussion
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...
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
OwenAuger
Super User
Super UserHi 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
- Zubair_Muhammad
Community Champion
Hi BI2018No
Just another way of doing it. ....
MEasure = SUMX ( VALUES ( Table1[Time] ), CALCULATE ( PRODUCT ( Table1[Value] ) ) )
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 )
jordancole0517Helper I
