Sum column based on unique IDs

walkery — Thu, 07 Jun 2018 20:20:45 GMT

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

Re: Sum column based on unique IDs

Greg_Deckler — Thu, 07 Jun 2018 20:31:05 GMT

You probably want to do something like:

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

Re: Sum column based on unique IDs

walkery — Mon, 11 Jun 2018 21:30:20 GMT

Thanks!

topic Sum column based on unique IDs in Desktop

Sum column based on unique IDs

Re: Sum column based on unique IDs

Re: Sum column based on unique IDs