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 DAX formulas doesnt do the same without using PQ.


Heres the sample data

 

Product_IDPriceItem_weightSales_Dept
123 R$    2,003Front Desk
123 R$    2,003Backshop
456 R$    3,004Front Desk
456 R$    3,004Backshop
789 R$    5,006Front Desk
789 R$    5,006Backshop
321 R$    8,009Front Desk
321 R$    8,009Backshop
654 R$    7,008Front Desk
654 R$    7,008Backshop
987 R$    1,002Front Desk
987 R$    1,002Backshop
258 R$    6,007Front Desk
852 R$  10,0011Backshop

 

For the sample data, the expected sum would be:

 

Price

Front: 32

Back: 10


Weight

Front: 39

Back: 11

 

One of the DAX formulas I tried is:

 

sum_price = SUMX(
    VALUES('Sample Data'[Product_ID]),
    CALCULATE( SELECTEDVALUE('Sample Data'[Price]) )
)

However the sum returned is the following:
 
Price
Front: 32
Back: 36
 
Weight
Front: 39
Back: 43
 

If I manually remove the duplicates (either in Excel or PQ), it will always keep the first entry and remove the second (of course), so for the product ID 123 only the front desk will be kept (expected and desired).

 

How to reproduce this behavior consistently in DAX?

  • 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)
        )
    )

4 Replies

    • P_Stavis's avatar
      P_Stavis
      Regular Visitor
      1 year ago

      Hi xifeng_L 

      I did download your sample pbix and also tried to reproduce but unfortunately I could only reach this point:

       

      I did get your idea of having 1 for the first line and 2 for the repeated line. For the sample data it would work however for a larger database i don't think it would be the best solution.
      Do you see another way to solve this problem only using dax measures?

      Thanks alot for your reply!

       

  • 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:

     

     

  • P_Stavis's avatar
    P_Stavis
    Regular Visitor
    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)
        )
    )