cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Bi2020 Frequent Visitor
Frequent Visitor

Dax to find total Units with out duplicates

Hi Team,

 

Please find the sample data,

 

Item CodeItem DescriptionSales PeriodPromotion NameSales UnitsForecast Units
10000PepsiP12019P12019 A1001325
100012 DISNEYP12019P12019 A290
1000220X16P12019P12019 A600
10019NXT EDPP12019P12019 A125109
10019NXT EDPP12019P12019 B125109
10021A G P12019P12019 A130210
10021A G P12019P12019 B130210
10023A G 50MLP12019P12019 A10196
      
   Total Inc Dup709 
   Actual454 

 

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

 

5 REPLIES 5
Community Support Team
Community Support Team

Re: Dax to find total Units with out duplicates

@Bi2020 ,

 

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

Capture.PNG 

 

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.

Bi2020 Frequent Visitor
Frequent Visitor

Re: Dax to find total Units with out duplicates

@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

Community Support Team
Community Support Team

Re: Dax to find total Units with out duplicates

@Bi2020 ,

 

Could you please give the expected result?

 

Regards,

Jimmy Tao

Bi2020 Frequent Visitor
Frequent Visitor

Re: Dax to find total Units with out duplicates

Hi Jimmy,

 

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

 

Thank you

Biju

Community Support Team
Community Support Team

Re: Dax to find total Units with out duplicates

@Bi2020 ,

 

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors