Forum Discussion

msciarrino's avatar
msciarrino
Helper I
2 years ago
Solved

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

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

msciarrino's avatar
msciarrino
Helper I
2 years ago

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:

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 :