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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

.

msciarrino's avatar
msciarrino
Helper I
2 years ago

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.