DAX - Remove duplicates and sum the values

[P_Stavis]

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_ID	Price	Item_weight	Sales_Dept
123	R$    2,00	3	Front Desk
123	R$    2,00	3	Backshop
456	R$    3,00	4	Front Desk
456	R$    3,00	4	Backshop
789	R$    5,00	6	Front Desk
789	R$    5,00	6	Backshop
321	R$    8,00	9	Front Desk
321	R$    8,00	9	Backshop
654	R$    7,00	8	Front Desk
654	R$    7,00	8	Backshop
987	R$    1,00	2	Front Desk
987	R$    1,00	2	Backshop
258	R$    6,00	7	Front Desk
852	R$  10,00	11	Backshop

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 · Accepted solution]

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)

    )

)

[xifeng_L]

Hi @P_Stavis 

You could try adding a calculated column to identify the row numbers in the interval:

Then just add the filter condition of row number equal to 1 in the measure:

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[P_Stavis]

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]

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: