Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Hi, could you please help me write sumproduct in DAX as it is shown in example (column "Result')? Thanks a lot
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] )
)
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! THANK YOU!!!!!
This helped solve my post below!
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 )
Hi @BI2018No
Just another way of doing it. ....
MEasure = SUMX ( VALUES ( Table1[Time] ), CALCULATE ( PRODUCT ( Table1[Value] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |