Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Sum Distinct On Duplicate Accounts
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?
Answers
-
Need more info to be able to help you ... what does your logical model look like?
0 -
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.
0