Forum Discussion
SUMPRODUCT Measure Using Fields from Same Table
I'm trying to create a Measure that calculates the SUMPRODUCT using fields from the same table.
Using an EXCEL layout as seen below, the formula used to calculate the ".024%" in D1 is: =SUMPRODUCT(A3:A26, B3:B26, D3:D26) / SUMPROUCT(A3:A26, D3:D26)
| A | B | C | D |
| 0.19% | 0.24% | ||
| Term | Rate | 12MoPmts | 24MoPmts |
| 1 | 0.0017 | 81440.81512 | 39704.72307 |
| 2 | 0.0017 | 81780.15185 | 39870.15941 |
| 3 | 0.0017 | 82120.90248 | 40036.28508 |
| 4 | 0.0017 | 82463.07291 | 40203.10293 |
| 5 | 0.0017 | 82806.66905 | 40370.61586 |
| 6 | 0.0017 | 83151.69684 | 40538.82676 |
| 7 | 0.001766667 | 83498.16224 | 40707.73854 |
| 8 | 0.001833333 | 83846.07125 | 40877.35412 |
| 9 | 0.0019 | 84195.42988 | 41047.67642 |
| 10 | 0.001966667 | 84546.24417 | 41218.70841 |
| 11 | 0.002033333 | 84898.52019 | 41390.45303 |
| 12 | 0.0021 | 85252.26402 | 41562.91325 |
| 13 | 0.002175 | 0 | 41736.09205 |
| 14 | 0.00224375 | 0 | 41909.99244 |
| 15 | 0.002319375 | 0 | 42084.61741 |
| 16 | 0.002395 | 0 | 42259.96998 |
| 17 | 0.002470625 | 0 | 42436.05319 |
| 18 | 0.00254625 | 0 | 42612.87008 |
| 19 | 0.002621875 | 0 | 42790.4237 |
| 20 | 0.0026975 | 0 | 42968.71713 |
| 21 | 0.002773125 | 0 | 43147.75345 |
| 22 | 0.00284875 | 0 | 43327.53576 |
| 23 | 0.002924375 | 0 | 43508.06716 |
| 24 | 0.003 | 0 | 43689.35077 |
I'm currently using the following formula in PowerBI but I'm getting different/incorrect results:
=SUMX('Table1', 'Table1'[Rate] * LOOKUPVALUE('Table1'[24MoPmts], 'Table1'[Term], 'Table1'[Term])) / SUMX('Table1', LOOKUPVALUE('Table1'[24MoPmts], 'Table1'[Term], 'Table1'[Term]))
This formula returns ".22%", but it should be ".24%".
Hi Anonymous ,
In that case, I suppose you don't need to include that 'Term' column in your sumproduct calculation either, and when that is removed, Power BI sumx calculation result and sumproduct calculation results are the same, although I would write dax formula in the following manner which is simpler.
Best regards,
3 Replies
Hi Anonymous
In your Excel formula, term is also included, but I would have throught that the overall rate calculation should be based on the amount and the rates only. I'd appreciate it if you could let me know why the indexing column of "Term" should also be included in the calculation.
Best regards,
DataNinja777 I'm not sure actually, that's just the exact formula that is being used in the Excel calculation but if it's not necessary to get to the correct answer then I suppose it's not applicable.
Hi Anonymous ,
In that case, I suppose you don't need to include that 'Term' column in your sumproduct calculation either, and when that is removed, Power BI sumx calculation result and sumproduct calculation results are the same, although I would write dax formula in the following manner which is simpler.
Best regards,