Remove duplicates from a SUMX function using columns from multiple tables in DAX

[msciarrino]

Hello All,

I am trying to create a measure were I get the sum of all the prices in one table and multiple it by the quantity of another table. I cannot combine the tables because the data set is too large.

I am currently using: 

Measure = SumX(Table1, Table1[Quantity] * Related(Table2[Price]))

This gives me a value, but it is currently summing the duplicate values in the price table. I cannot remove the duplicate values in the price table via power query because I list them out in a different section.

How can I remove the duplicates from the SUMX function?

EDIT: Replaced Sample Data Image to correct version

Sample Data:

Thanks

[Anonymous · Accepted solution]

Hi @msciarrino ,

Please try to create measure with below dax formula:

Measure =
VAR cur_id =
    SELECTEDVALUE ( Table1[ID Numbers] )
VAR _a =
    CALCULATE (
        MAX ( Table1[Quantity] ),
        FILTER ( ALL ( Table1 ), [ID Numbers] = cur_id )
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table 2'[Price] ),
        FILTER ( ALL ( 'Table 2' ), [ID Numbers] = cur_id )
    )
VAR _r1 = _a * _b
VAR tmp =
    SUMMARIZE (
        ALL ( Table1 ),
        Table1[ID Numbers],
        "QTY", MAX ( Table1[Quantity] )
    )
VAR _c =
    CALCULATE ( MAX ( Table1[Quantity] ), FILTER ( tmp, [ID Numbers] = cur_id ) )
RETURN
    _r1

Measure 2 = SUMX(VALUES(Table1[ID Numbers]),[Measure])

Add a table visual with fields and measure:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Power5]

.

[msciarrino]

This will work, but I want to avoid creating another table due to the large amount of data I am using. I would like to do it as a measure (or calculated column) if possible.

[Power5]

You aren't creating another table. You are merging queries into one.

[Power5]

What is the result you want?

[msciarrino]

Here is a more realistic version of my data and what I am seeing.

Original Data Tables:

Table Visulation with my original sumx formual:

Price*Quantity = SumX(Table1, Table1[Quantity] * Related(Table2[Price]))

My original formula is multiplying the quantites by the price, but since some of the rows are duplicated in Table 1, they are counting it twice.

Table visualization using the formula you provided:

Price*Quantity = DISTINCTCOUNT('Table 2'[ID Number]) * SUM('Table 1'[Price($)])

I belive that you formula is taking the Total of the plan Price column and multiplying by the number of IDs, in this case 10 making the sum just 10x of the total price column.

Table visulation I want:

[Power5]

Okay, lets try this. I think this will work better.

1. Did you merge your tables? and remove duplicates?

2. Create first measure = 

Measure 1 = SUMX('Table 1','Table 1'[Quantity] * 'Table 1'[Table 2.Price($)])

3. Create second measure = 

Measure 2 = SUMX(VALUES('Table 1'[Table 2.Price($)]),[Measure1])

4. Add fields to a table.

5. My results : 

[Power5]

.

[msciarrino]

That did not seem to work for me. When I use that, I get what looks like 10X the value of just adding the price without worrying about the quanties. And there are 10 items in the filtered table I am looking at. I believe that this is counting the items showing then multiplying it by sum of everything quantity 1.

I apoligize. I screwed up my sample data. I reversed the quantity and price. The data should look like this.