calculate sum based on distinct values of another column

[powerbiss]

Hi,

Is it possible to get some help in creating a DAX formula for the attached problem that I came across in power bi.

I have two columns in my sample database (expense_id and amount). I have duplicate values in expense_id column and as well as duplicate values in amount column. I want to sum the amount column that has distinct values in expense_id column. I have attached a screenshot.

Really appreciate all your help. Thanks

[parry2k]

@powerbiss here is the measure:

Unique Sum Amount = SUMX ( SUMMARIZE ('Table','Table'[expense_id], 'Table'[amount] ), 'Table'[amount] )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Syndicate_Admin]

Hello good afternoon, which is what I should do if I want to add the values of more than 1 single column, as shown in the example, what should I do if I have 2 columns called amount

[smpa01]

@powerbiss  try this measure

Measure = SUMX(GROUPBY('Table','Table'[expense id],'Table'[amount]),[amount])

[Syndicate_Admin]

Hello good afternoon, which is what I should do if I want to add the values of more than 1 single column, as shown in the example, what should I do if I have 2 columns called amount

[powerbiss]

Hello @powerbiss

For some reason it is showing 285.80 in the all rows. It is not showing 250 for 17 and 35.8 for 19.

[smpa01 · Accepted solution]

@powerbiss  not sure what you meant

[powerbiss]

Hey @smpa01 

That worked. had some issues with creating the relationship with the tables.