Dax to find total Units with out duplicates

[Anonymous]

Hi Team,

Please find the sample data,

Item Code	Item Description	Sales Period	Promotion Name	Sales Units	Forecast Units
10000	Pepsi	P12019	P12019 A	100	1325
10001	2 DISNEY	P12019	P12019 A	29	0
10002	20X16	P12019	P12019 A	60	0
10019	NXT EDP	P12019	P12019 A	125	109
10019	NXT EDP	P12019	P12019 B	125	109
10021	A G	P12019	P12019 A	130	210
10021	A G	P12019	P12019 B	130	210
10023	A G 50ML	P12019	P12019 A	10	196
			Total Inc Dup	709
			Actual	454

You can see the item codes 10019,10021 are duplicating .I want to find out the Sum of Sales Unts with out duplicates.As you can see the expected result as well in the grid.Can some one suggest me the best DAX formula to achieve this.

Thank you

[v-yuta-msft]

@Anonymous ,

You could create a measure using dax below:

Total Inc Dup = 
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Sales Units],
        "Item Code", DISTINCT ( 'Table'[Item Code] )
    ),
    'Table'[Sales Units]
)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yuta-msft,

Thank you for the reply.

Actully the above shown girds are showing the sum of sales units against each item code,so the suggested DAX not applicable.

Thank you

[v-yuta-msft]

@Anonymous ,

Could you please give the expected result?

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy,

Expected result it 494 but im not getting the result in the actual data with your formula.

Thank you

Biju

[v-yuta-msft]

@Anonymous ,

I'm afraid I still couldn't understand your requirement, could please explain how to achieve 494 sales total and could you share the complete sample data for further test?

Regards,

Jimmy Tao