Sum of Values Excluding Duplicates with Filtering Across Tables

[mryd3r]

Hi, I'm hoping to summarise the data below to produce a report which displays the total Team Win Values.

[BUT... without the duplicates where there may have been two team members on one Opportunity]

Hopefully the below tables and expected outputs can explain what I'm trying to achieve.

I have a visualisation a bar graph of all members of the team with the count of their wins in current year, from which I'd like to be able to have the ability to click on the Team member and filter the table to their specific wins and then total their own overall Win Value.

EDIT FROM Original Post:  I forgot to mention that there is also more data for other Users (Outside of this Team, so I need to be able to filter to selected individuals that make up this Team, which I've done using a Filter on the Page for the selected people I'm trying to report on).

Table Account Name				Table Opportunities						TABLE:  USERS
Account ID	Account Name	Opportunity ID	One to Many	Opportunity ID	Account Name	Opportunity Name	Opportunity Value	Team Member ID	One to Many	Team Member ID	Team Member Name
Account 1	Account NAME 1	Opp 3462		Opp 3462	Account 1	Opp Name RED	10000	2		1	David
Account 2	Account NAME 2	Opp 5628		Opp 5628	Account 2	Opp Name YELLOW	15000	5		2	Emma
Account 3	Account NAME 3	Opp 2751		Opp 5628	Account 2	Opp Name YELLOW	15000	6		3	Paul
Account 4	Account NAME 4	Opp 8759		Opp 2751	Account 3	Opp Name BLUE	50000	4		4	Helen
Account 5	Account NAME 5			Opp 8759	Account 4	Opp Name ORANGE	30000	7		5	Ian
Account 6	Account NAME 6			Opp 8759	Account 4	Opp Name ORANGE	30000	1		6	Lucy
Account 7	Account NAME 7									7	John

At the moment in the below table I'm getting the Total Value (INCLUDING duplicates) so 150,000, instead of the Unique Wins Value of 105,000.

Expected Output
Team Member Name	Team Member ID	Account Name	Opportunity Name	Value
Emma	2	Account NAME 1	Opp Name RED	10000
Ian	5	Account NAME 2	Opp Name YELLOW	15000
Lucy	6	Account NAME 2	Opp Name YELLOW	15000
Helen	4	Account NAME 3	Opp Name Blue	50000
Ian	5	Account NAME 4	Opp Name Orange	30000
David	1	Account NAME 4	Opp Name Orange	30000
Sub Total All Unique Opportunities	105000
With ability to also filter based on selected Team Member Name
Team Member Name	Team Member ID	Account Name	Opportunity Name	Value
Ian	5	Account NAME 2	Opp Name YELLOW	15000
Ian	7	Account NAME 4	Opp Name Orange	30000
Sub Total for just Ian	45000

Thanks in Advance.

Regards

mryd3r

[amitchandak]

@mryd3r , Try a measure like

sumx(values(Table[Opportunity ID]), calculate(max(Table[Opportunity Value])))

[mryd3r]

Hello @amitchandak , I've edited the original post as I forgot to mention that:

EDIT FROM Original Post:  I forgot to mention that there is also more data for other Users (Outside of this Team, so I need to be able to filter to selected individuals that make up this Team, which I've done using a Filter on the Page for the selected people I'm trying to report on).

Hope this makes sense.

Your measure suggested does reduce the total sum of my actual data but it reports on every piece of data in the Opportunities Table, without filtering down to the selected individuals in the Users Table.