How to find SUMPRODUCT of two columns in DAX

Question

I am new to DAX formulas. I am looking to see how I can do an excel equivalent of a SUMPRODUCT for the following data:

Id | Metric | Weight | Metric times Weight |  
1 | 20% | 30% | 6.0% 

2 | 10% | 20% | 2.0%  

3 | 25% | 20% | 5.0%  

4 | 12% | 10% | 1.2%  

5 | 15% | 10% | 1.5%  

6 | 2% | 10% | 0.2% |  | 


Net | 84.0% | 100.0% | 84.0% (expected Metric*Weight)

I need 15.9% which is SUMPRODUCT(col1, col2).

The row total needs to be a sumproduct() versus the expected cross product above.

Any hints how I can achieve this?

Answer 1

If the data is on a table called Table1 then the following DAX formula should work:

WeightedAvg := SUMX(Table1, Table1[Metric] * Table1[Weight])

Basically, the function SUMX is iterating over the table Table 1 doing the product of [Metric] * [Weight] then once all the iterations are done, the results are added.

If you have a column with the product (as in your example), then you just need to drag that field to the values area of the PivotTable.

Hope this helps!