Check for a duplicate within powerpivot on the fly

Question

In my organization various sales personnel could be part of many sales transactions, and I have a table below that shows for each sales personnel the amount revenue they were part of. However, depending on how a user is using the table, the pivot may double/triple count revenue data.

As an example when a user filter on SalesManager1, there will be a duplicate as both SalesPersonnel 1 and 3 have sold ProductA. However, When a filter on just SalesPersonnel1, there's no double count. similarly, when a user selects SalesManager4 although he has 2 Sales personnel reporting to him/her they were part of distinct sales transactions thus there's no duplicate.

I want to create a measure which flags when there's a potential duplicate in the view users are looking at. However, I cannot go back and change how the data model was built. Is there a way to do this all within the data model? I know I can probably create a pivot table share the filter/slicer, and then count # of sales personnel for the accounts and products, but I'm hoping for more of sophisticated solution... would appreciate any help/support you can provide!

thank you

SalesManager  SalesPersonnel   Acccount   Product  Revenue
SalesManager1 SalesPersonnel1 Acccount_A Product_A 100000
SalesManager1 SalesPersonnel1 Acccount_B Product_C 100000
SalesManager1 SalesPersonnel3 Acccount_A Product_A 100000
SalesManager2 SalesPersonnel3 Acccount_B Product_C 100000
SalesManager1 SalesPersonnel2 Acccount_B Product_C 100000
SalesManager1 SalesPersonnel2 Acccount_B Product_C 100000
SalesManager4 SalesPersonnel4 Acccount_B Product_A 100000
SalesManager4 SalesPersonnel4 Acccount_A Product_D 100000
SalesManager4 SalesPersonnel5 Acccount_A Product_B 100000
SalesManager4 SalesPersonnel5 Acccount_A Product_A 100000

Bob Phillips · Answer 1 · 2015-12-09T18:54:00.010

0

You could create a simple measure to count salespersons

  CountSalesPersonnel:=IF( COUNTA( Table1[SalesPerson] ) > 1, "Oops, "")

edited Dec 09 '15 at 18:54

answered Dec 09 '15 at 18:47

Bob Phillips

437
1
3
7

If I go with this approach, and if a user selects SalesManager4, wouldn't it show up as "Oops" even though there's no duplicate? – user2669043 Dec 09 '15 at 22:41
You are right, but SalesManager4 does have duplicates for Product A, so you would need to include Product in the pivot and maybe try Duplicates:=IF(DISTINCTCOUNT( Table1[SalesPerson] ) > 1, "Oops, "") – Bob Phillips Dec 09 '15 at 23:57
Product A isn't duplicate though, because it was sold to Account B. When Sales Manager 4 is chosen, there is no duplicate. Sales Personnel4 sold Product A to AccountB and Product D to Account A. SalesPersonnel5 sold Product B and Product A to Account A. – user2669043 Dec 13 '15 at 19:47

score 0 · Accepted Answer · answered Dec 13 '15 at 19:43

Sorry to answer my own question, but here is what I did. I've taken the hint from the above poster, and arranged it to my own need.

here is what I did: I added a new column that would combine Account, Product, and Revenue

ConsldforDupeCheck:=Acccount&Product&Revenue

and then created a measure

DoubleCountCheck = if(CALCULATE(DISTINCTCOUNT([ConsldforDupeCheck]))=COUNTROWS(Table),"","*PossibleDoubleCountError*")

this allowed to have a measure that checks the duplicate on the fly. Thanks Bob for the hint!!

Well you got a solution, hope my efforts helped :-) – Bob Phillips Dec 13 '15 at 22:16 — Bob Phillips, Dec 13 '15 at 22:16

Check for a duplicate within powerpivot on the fly

2 Answers2