Hi, I’m trying to count the number of opportunities that have a task with at least one “complete” status, but I don’t want to count it twice if there are two tasks for that opportunity with “complete” status. Then I am grouping by user assigned to the task in a table visual to see how many unique opportunities they have completed a task for. I’m struggling to figure out how to get my calculated field to only count each opportunity once when the user has multiple complete tasks.
My data looks something like this:
- Task1, Opp1, User1, complete
- Task2, Opp2, User1, incomplete
- Task3, Opp1, User1, complete
- Task4, Opp3, User2, incomplete
- Task5, Opp4, User2, complete
- Task6, Opp4, User2, incomplete
- Task7, Opp5, User1, complete
- Task8, Opp6, User2, incomplete
Then when I show the data in a table per user, I would want to my calculated field to show:
User1 = 2
User 2 = 1
Because that’s the number of unique opportunities they have at least one completed task for. Any suggestions on how to accomplish this?