Forum Discussion
SumProduct DAX formula needed
Hi Team,
I need help on creating SumProdut same as excel
Table1
| Date | EUR | USD | GBP | Total |
| 6/1/2017 | 0.00 | -17375965.81 | 24889.80 | -17343852.99 |
| 6/2/2017 | 0.00 | -17375965.81 | 16.52 | -17375944.53 |
| 6/3/2017 | 0.00 | -17375965.81 | 16.52 | -17375944.53 |
| 6/4/2017 | 0.00 | 3898944.39 | 16.52 | 3898965.67 |
| 6/5/2017 | 1662538.33 | 1508133.98 | 93203.45 | 3499001.66 |
| 6/6/2017 | 9427560.61 | 432590.10 | 398.45 | 11054193.64 |
| 6/7/2017 | 561163.03 | 2771843.67 | 64267.92 | 3487159.96 |
| 6/8/2017 | -159730.24 | 1949435.12 | 555.39 | 1770920.83 |
| 6/9/2017 | 887221.94 | 2268106.63 | 572.13 | 3261015.88 |
| 6/10/2017 | 887221.94 | 2268106.63 | 572.13 | 3261015.88 |
Table 2
| Date | EUR | USD | GBP |
| 6/1/2017 | 1.1225 | 1 | 1.2902 |
| 6/2/2017 | 1.1275 | 1 | 1.2881 |
| 6/3/2017 | 1.1275 | 1 | 1.2881 |
| 6/4/2017 | 1.1275 | 1 | 1.2881 |
| 6/5/2017 | 1.125 | 1 | 1.293 |
| 6/6/2017 | 1.1266 | 1 | 1.2894 |
| 6/7/2017 | 1.1263 | 1 | 1.2958 |
| 6/8/2017 | 1.1221 | 1 | 1.2946 |
| 6/9/2017 | 1.1183 | 1 | 1.2741 |
| 6/10/2017 | 1.1183 | 1 | 1.2741 |
as for the sumproduct in excel, it works as an array and calculates Table1.EUR * Table2.EUR and gives the result, similarly it calculate for other columns and give the sum of all the values. however, I am unable to replicate this in DAX.
The output should be Table1. Total
Please help .
Anonymous there is may ways to do this, assuming these two tables are related on date with 1 to 1 relationship, you can add following column:
Total = (Table6[EUR] * RELATED( Table7[EUR] ) ) + (Table6[GBP] * RELATED( Table7[GBP] ) ) + (Table6[USD] * RELATED( Table7[USD] ) )
4 Replies
- parry2k
Super User
Anonymous is date unique in both tables?
- parry2k
Anonymous there is may ways to do this, assuming these two tables are related on date with 1 to 1 relationship, you can add following column:
Total = (Table6[EUR] * RELATED( Table7[EUR] ) ) + (Table6[GBP] * RELATED( Table7[GBP] ) ) + (Table6[USD] * RELATED( Table7[USD] ) )