SUMX + DISTINCT function

[jroz2001]

Goodday,

I want to use the SUMX function in combination with DISTINCT (PowerPivot + Excel 2010).

For example I have this data:
[TABLE="width: 250"]
<tbody>[TR]
[TD]Productgroup[/TD]
[TD]Product[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Group Y[/TD]
[TD]product1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Group Y[/TD]
[TD]product2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Group X[/TD]
[TD]product1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Group X[/TD]
[TD]product3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

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:

=SUMX(DISTINCT('table'[Product]), [Sales])

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!

 

[Matt Allington]

Wow, interesting problem. Sumx as you have described won't work. The challenge is to determine a set of rules that uniquely identifies each product so you know if you have to sum it or not. The problem I have is that it is not clear what these rules are. Is is it possible to have 2 more rows as follows

group y. Product 1. 4
group X. Product 1. 4

And what about these

group y. Product 1. 3
group X. Product 1. 4

Do you have any other unique identifying Information or anything else you can share

 

[Ozeroth]

Hi jroz and Matt,

It sounds like you have a many-to-many relationship between Product and Productgroup, so a many-to-many model with separate Product & Productgroup tables & a bridging table should work here.

See this paper page 98 for an example of the model setup:
http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf
Comparing your model with the one in the paper:

• Dim_Account would become Product
• Dim_Customer would become Productgroup
• Bridge_AccountCustomer would become Bridge_ProductgroupProduct

Your Sales table would just contain sales by Product, without needing to restate each sale for every group that product is in. (The bridge table captures that instead.)

See page 103 for an example of the actual many-to-many measure.
The SUMMARIZE(...) bit can be changed to just the name of the bridge table.

Here's an attempt in Excel 2010 with your sample tables:
https://www.dropbox.com/s/bo9q2uozy3m7rh6/Product Productgroup Many-to-many (excel 2010).xlsx?dl=0

 

[jroz2001]

Ok, I figured it out! i don't know exactly how the formula works, but it works
First I create a New Measure with, in this example the name 'maxsales'

=MAXX(DISTINCT('table'[Product]),MAX('table'[Sales]))

Then I create another new measure:

=SUMX(DISTINCT('table'[Product]),[maxsales])

Thanks anyway for trying to help me out!

 

[Matt Allington]

Well it works by ignoring one of the rows of duplicate data. Minx, averagex would work for similar reasons