topic Re: SumProduct DAX formula needed in DAX Commands and Tips

SumProduct DAX formula needed

Anonymous — Tue, 07 May 2019 16:48:26 GMT

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 .

Re: SumProduct DAX formula needed

parry2k — Tue, 07 May 2019 18:14:09 GMT

Anonymous is date unique in both tables?

Re: SumProduct DAX formula needed

parry2k — Tue, 07 May 2019 18:19:11 GMT

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

Re: SumProduct DAX formula needed

Anonymous — Wed, 08 May 2019 05:58:42 GMT

Yes

Re: SumProduct DAX formula needed

Anonymous — Wed, 08 May 2019 06:02:12 GMT

Thank you... This works

however, do we have an exact replica of SUMPRODUCT?