Sum Distinct On Duplicate Accounts

DentsplySirona

Trying to create a setup where I have a Sales Rep. that may cover another territory.  These territories are defined by Customer Accounts.  The same account may be in 2 different territories.   As long as the Sales Reps names are on the report the totals will be accurate. The Grand Total will not.  But if the Sales Reps are not on the report the Totals will have some Sales duplication.  This is due the the fact the the same account sale will be on each Rep. 

Example:

Rep 1

          Account 1:  $1000

          Account 2:  $1000

          Account 3: $1000

Rep 2:

          Account 3: $1000

          Account 4: $500

          Account 5:  $500

Rep 1 is covering Rep 2's territory

Report 1:

     Rep.          Total

     Rep 1          $3000

     Rep 2          $2000

     Total             $5000   (This should be $4000)

Report 2:

     Total          $5000    (This should be $4000)

How can I setup the BMM to do a Sum Distinct on the sales by Account here so my Total removes the account duplication?

Thomas Dodds

Need more info to be able to help you ... what does your logical model look like?

Souvik Sarkar

In such a situation where two dimensions are conflicting with each other, you must use both the dimensions together to be able to show the distribution of measure in a correct manner. If you include the Account column in Report 1, the data would be represented correctly. Logically speaking, if 2 different Reps make a sale under the same account, the sale made by both the Reps are valid and the total should be considered to attain the Total Sales. If you want to show the distinct Sales irrespective of the no. of Reps, you need to take the mean/average of the Total Sales by the no. of Reps for a particular account. In such case, you need to use Aggregate/Sum(Sales by Rep) for the measure column. I hope this somewhat answers what you were looking for.