Forum Discussion

o59393's avatar
o59393
Post Prodigy
2 years ago
Solved

Calculate total without duplicates

Hi all

 

I have the following table and I'd like to have the total for the column 'Order (KG)' without duplicate values given by column I:

 

 

The desired value is C11:

 

 

How can I have this total without the duplicates?

 

Thanks.

 

Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Revise Measure 2 to

    Measure 2 = SUMX(SUMMARIZE(Data,Data[Date],Data[Material],Data[Order (kg)],Data[Supplier],Data[Proit centre],Data[Order No],"ABCD",[Measure 1]),[ABCD])

    Hope this helps.

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Try these measure

    Measure 1 = min(Data[Order (kg)])

    Measure 2 = SUMX(VALUES(Data[key]),[Measure 1])

    Hope this helps.

    • o59393's avatar
      o59393
      Post Prodigy
      2 years ago

      Hi Ashish_Mathur 

       

      Question. Instead of using the [Key] column value, is it possible to use within the measure the concatenation of the different columns?


      In this specific case: Date&Material&Order (kg)&Supplier&Profit Center & Order # ?

       

      Something like:

       

      OrderKG_NoDuplicates = 

SUMX(
    VALUES(
        'Orders'[Date]& Orders'[Material] & Orders'[Supplier] & Orders'[Order (KG)] & Orders'[Profit Center] & Orders'[Order #)]),
     
     min(Orders[Order (KG)])
     )

       

      Thanks!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        Revise Measure 2 to

        Measure 2 = SUMX(SUMMARIZE(Data,Data[Date],Data[Material],Data[Order (kg)],Data[Supplier],Data[Proit centre],Data[Order No],"ABCD",[Measure 1]),[ABCD])

        Hope this helps.