Forum Discussion

P_Stavis's avatar
P_Stavis
Regular Visitor
1 year ago
Solved

DAX - Remove duplicates and sum the values

Hello!   I'm trying to use DAX to remove the duplicates, based on a column and then sum the values of another column. Manually removing the duplicates in PQ returns the correct values however my D...
  • P_Stavis's avatar
    P_Stavis
    1 year ago

    Heres the formula that did the job:

     

    For price:

     

    Sum_Price_Ok =
    SUMX(
        DISTINCT('Sample Data'[Product_ID]),
        CALCULATE(
            FIRSTNONBLANK('Sample Data'[Price], 0)
        )
    )
     
    For the weight:
     
    Sum_Weight_Ok =
    SUMX(
        DISTINCT('Sample Data'[Product_ID]),
        CALCULATE(
            FIRSTNONBLANK('Sample Data'[Item_weight], 0)
        )
    )
P_Stavis's avatar
P_Stavis
Regular Visitor
1 year ago

If I may add something based on my findings...

If I aggregate using groupby or summarize with only the price and product_id and show the data in a calculated table, the duplicates are removed:

If I add the sales dept then the duplicates return: