PowerPivot and DAX papers,posts,FAQs,webcasts,samples

Good morning,
I have issues with the following:

I have a big raw database that contains reference numbers and the weight associated with it. I want to be able to show the total Weight (values contained in the same table). But there can be duplicates in the reference numbers. So summing up the column Weight wouldn't give me the correct result. And i can't take the distinct column Weight either since a Weight_value can be the same for several Reference nubmers. I thought of the Function SUMX, with the following formula:
=SUMX(DISTINCT(Table1(Reference numbers)),COUNT(Table1(Reference Weight))
But it gives me a wrong answer.

to be clear, if i could do it manually, I would do:
Take Table1, remove duplicates of Reference numbers, then the correct result would be the sum of the remaining values in the column Weight.

I don't know how to fix this.
Thank you for helping me out here

Hi there,

If you try to do the same stuff in MS-Excel, each column that has reference number will give you sum of all weighted values in each row using SUMIF function. The same will work when you use SUMX function in Powerpivto.

My suggestion would be

1) Create another tab in powerpivot with unique reference numbers
2) Create a relationship between table 1 and table 2 (Table 1 - Reference number with multiple occurences and weighted scores of each, Table 2 - Unique Reference numbers)
3) Now use SUMX function to get the sum of weighted scores of each Reference number in table 2

Hope this helped!!!