cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
walkery Regular Visitor
Regular Visitor

Sum column based on unique IDs

Hi: I have a dataset with 50K records in it. Some of those records have duplicate IDs, leaving only 47K unique values, they just have a different employee attached to them thus resulting in a duplicate ID. I'd like to create a card on my dashboard that shows the total revenue, while not double counting the duplicate records. Example table below. With this example the card would ideally display 14,500, only summing the revenue column for the first instance of a Deal ID. I thought it should be simple but I've tried other solutions posted on this forum with no luck. Thanks. 

 

Deal ID           Revenue     Employee
12345             1,000          Jon Smith
12345             1,000          Bill Jones 
12346            12,000          Jon Smith
12555             1,500           Jane Doe 
12555             1,500           Michael Scott
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sum column based on unique IDs

You probably want to do something like:

 

Measure = SUMX(SUMMARIZE(Table,[Deal ID],"__Revenue",AVERAGE([Revenue])),[__Revenue])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Super User
Super User

Re: Sum column based on unique IDs

You probably want to do something like:

 

Measure = SUMX(SUMMARIZE(Table,[Deal ID],"__Revenue",AVERAGE([Revenue])),[__Revenue])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


walkery Regular Visitor
Regular Visitor

Re: Sum column based on unique IDs

Thanks!