Total Challenges on SUM of DISTINCT VALUES

[Pam]

I have two datasets that were pulled together (via append). I had to append instead of make a measure, etc to make other calculations work. 

• Dataset #1: contained all Lead group sales
• Dataset #2: contained all Secondary group sales

  Ex Dataset looks something like this: 

  

  I have multiple Opportunity IDs but I'm trying to get the SUM of DISTINCT VALUES so used the calc below. It works as expected at the row level but goes wonky when it gets to the subtotal value. 

Total Sales_SUMx/DISTINCT =
SUMX (
    (DISTINCT('Sales'[Opportunity ID])) , MAX('Sales'[Total Sales Amount])
)

Using the sample dataset above, I would expect to see the Total of column 'Total Sales Amount' to read $1,950. In my actual dataset, I am expecting to see values of $3 Billlion; but am seeing values of $913 Trillion - it's off by a huge multiple. 

I'm no expert so have done I've read a ton of forums and articles and I suspect its doing some additional calculations 'under the covers' but I am having trouble figuring out how to get the total to work in the matrix table my users are requesting.

I have found articles with 'Total' fixes but none that reference the DISTINCT function. It also has to be slicable by additional filters like Client Name, etc so I'm running into challenges with functions like 'HASONEFILTER'. 

 

I'm out of ideas, any help is appreciated!

[Pam]

@v-tangjie-msft 
Neeko Tang, 
That did it! Thank you for your help, I really appreciate it!

[v-tangjie-msft · Accepted solution]

Hi @Pam ,

You can create a new measure to be placed on the visual object instead of the old measure.

NewMeasure = SUMX(VALUES('Sales'[Opportunity ID]),[Total Sales_SUMx/DISTINCT])

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.