Sum distinct based on the values of two columns

[byr10112]

I have a table that shows annual sales and quota for each account. Each account also has a number of sales opportunities linked to them. I am trying to include all the information in one table, so I merged two queries into one. The problem is now the sales and quota numbers are being double, sometimes triple counted. Below is an example of what my data looks like:

Account ID	Segment	Sales	Quota	Opportunity ID	Opportunity $
1001	X1	100	80	XYZ	200
1001	X2	150	90	XYZ	200
1001	X1	100	80	XXX	210
1001	X2	150	90	XXX	210
1001	X1	100	80	CRD	150
1001	X2	150	90	CRD	150

I'm trying to create a measure that will only sum the sales (and another one for quota) based on Account ID and Segment. So ideally, in the background it should look like this:

Account ID	Segment	Sales	Quota
1001	X1	100	80
1001	X2	150	90
Total		250	170

How can I create a measure to only display the unique sales for accountID and Segment?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

One of ways to solve this is to summarize the table to show distinct information.

Sales measure: = 
SUMX ( SUMMARIZE ( Data, Data[Sales] ), Data[Sales] )

Sales measure: =
SUMX ( SUMMARIZE ( Data, Data[Sales] ), Data[Sales] )