Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
4 years ago
Solved

Remove Duplicate Values in Column

Hello,   I am trying to create a table by Client ID using the data in the top table of the screenshot below.     When summing the eligible amount by client id, I receive a value of $10,000,000 fo...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    gmasta1129, two ways to achieve what you're after is:

     

    1. Create a unique key that merges Client ID and Post Code, OR

    2. Use MAX to return the maximum amount for the respective Client ID.

     

    Firstly, though, I recommend you attempt the earlier options I provided so that assumptions are tested for fact and proven not to work.

     

    All the best with your challenge.

     

    Theo 🙂

     

TheoC's avatar
TheoC
Community Champion
4 years ago

Hi gmasta1129 

 

Okay, so I've put the following Calculated Column together for you and provided you with the output and how it achieves what you are after:

 

 

Return Max Value = 

VAR _1 = CALCULATE ( COUNTROWS ('Table' ) , FILTER ('Table' , 'Table'[Client ID] = EARLIER ('Table'[Client ID] ) ) )

RETURN

IF ( _1 = 1 , 'Table'[Amount] , MAX ('Table'[Amount] ) )

 

 

The AMOUNT column is all amounts to each Client ID added.  I've purposely made Client ID "2" have two records of the same value (i.e. 32 and 32 which total equals 64, similar to your $5m and $5m).

 

 

All the best and I hope this achieves the solution you're after 🙂

Theo