Goodday,
I want to use the SUMX function in combination with DISTINCT (PowerPivot + Excel 2010).
For example I have this data:
<tbody>
</tbody>
Products can be assigned to multiple Productgroups. So product1 is in both, Group Y and Group X. If I create a PivotTable with PowerPivot, the total Sales of Group Y is 9, and total Sales of Group X is 8. However, overall total Sales is 17, while I want it to be 12 (so count product1 only once).
I found out that I should SUMX in combination with DISTINCT, so I created this one:
A new column is created, however, the values, including totals are equal with the ones in de Sales-column. I hope someone can help me with this!
Thanks in advance!
I want to use the SUMX function in combination with DISTINCT (PowerPivot + Excel 2010).
For example I have this data:
Productgroup | Product | Sales |
Group Y | product1 | 5 |
Group Y | product2 | 4 |
Group X | product1 | 5 |
Group X | product3 | 3 |
<tbody>
</tbody>
Products can be assigned to multiple Productgroups. So product1 is in both, Group Y and Group X. If I create a PivotTable with PowerPivot, the total Sales of Group Y is 9, and total Sales of Group X is 8. However, overall total Sales is 17, while I want it to be 12 (so count product1 only once).
I found out that I should SUMX in combination with DISTINCT, so I created this one:
Code:
=SUMX(DISTINCT('table'[Product]), [Sales])
Thanks in advance!