Forum Discussion
Using SUMPRODUCT in dax
Hi All,
I would like to compile a similar table to PowerBI but I need to calculate the totals using DAX. I'm running to an issue especially with the row level context and the total Avg amounts for both Products A & B as I use SUMPRODUCT(Product A:AVG Amount ProductA)/SUM(Product A) = $22.67
Expected result Total AVG amount per month (Jan) = ((80*22)+(64*20))/144 = $21.11
Entire year Avg amount =( (3282*19.90)+(2798*19.90))/ (3282+2798)
| Product A | Product B | Avg Amount- Prod A | Avg Amount- Prod B | Avg Amount -All Products | |
| Jan | 80 | 64 | 22 | 20 | 21.11 |
| Feb | 41 | 211 | 22 | 17 | 17.81 |
| Mar | 58 | 200 | 22 | 22 | 22.00 |
| Apr | 879 | 233 | 22 | 22 | 22.00 |
| May | 88 | 400 | 23 | 23 | 23.00 |
| Jun | 89 | 74 | 23 | 15 | 19.37 |
| Jul | 547 | 146 | 23 | 19 | 22.16 |
| Aug | 742 | 874 | 23 | 20 | 21.38 |
| Sep | 214 | 20 | 23 | 17 | 22.49 |
| Oct | 200 | 222 | 23 | 14 | 18.27 |
| Nov | 100 | 100 | 23 | 22 | 22.50 |
| Dec | 244 | 254 | 23 | 20 | 21.47 |
| Total | 3282 | 2798 | 22.68 | 19.90 | 21.40 |
Please help create a measure
see attached. Note that your source data structure is not sustainable.
1 Reply
see attached. Note that your source data structure is not sustainable.