Sum of distinct values for each customer id

[bml123]

I have data like this and I and I want the output as in Total column where it should sum only the distinct values from amount for each id.  How do I achieve this?

Id	Amount	Total
1	10	30
1	10	30
1	20	30
1	10	30
2	20	50
2	20	50
2	30	50
2	20	50
2	20	50
2	30	50

[Jihwan_Kim · Accepted solution]

Hi, @bml123 

Please check the below picture and the formula for creating a new column.

Total CC =

VAR currentid = 'Table'[Id]

VAR distinctamount =

FILTER (

SUMMARIZE ( 'Table', 'Table'[Id], 'Table'[Amount] ),

'Table'[Id] = currentid

)

RETURN

SUMX ( distinctamount, 'Table'[Amount] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@bml123 , Try a new measure like

sumx(summarize(Table, Table[ID], Table[Amount]),[Amount])

[bml123]

@amitchandak, I have tried your solution but it is not working.

[amitchandak]

@bml123 , if need a column, That was measure

Column = sumx(DISTINCT(SELECTCOLUMNS(filter('Table',[Id] =EARLIER('Table'[Id])),"Amt",[Amount])),[Amt])