cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adris-misra
Frequent Visitor

Need to find Sales Rep count who are selling more than one product

Hello folks - Need one help with calculating a DAX measure.

 

I have a Sales Rep Table: 

RepIDRepName
1001Sam
1002Paul
1003Hans
1004Ram
1005**bleep**
1006John
1007KK
1008Ether
1009Nancy
1010Kim
1011Su

 

A Product Table:

ProdIDProdName
5001Milk
5002Egg
5003Salad
5004Dress
5005Shoe

 

and one sales table (fact)

SalesIDProdIDRepID
10150041006
10250051007
10350031010
10450021009
10550031003
10650011008
10750051004
10850051004
10950051010
11050021001
11150021002
11250011003
11350051010
11450031009
11550041007
11650031005
11750011004
11850031004
11950041004
12050021001
12150021001
12250021001
12350031006
12450051002
12550031003
12650031003
12750011005
12850051009
12950031009
13050011008

 

I am trying to show count of sales rep who are selling more than one product. I can show in a table as individuals who are selling more than one product, but when I try to get the count of sales rep, that is where I am strugging. Please see the excel file and pbix attached for you reference. Appreciate any help calculationg the measure, so I can show the count in a card visual.

Attachments 

1 ACCEPTED SOLUTION
Payeras_BI
Super User I
Super User I

Hi @adris-misra ,

Payeras_BI_0-1614119409941.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

3 REPLIES 3
daxer-almighty
Solution Sage
Solution Sage

// Assumptions:
// All ID columns should be hidden and
// all slicing must be done through
// dimensions. Fact tables should (almost)
// always be hidden unless they contain
// degenerate dimensions (but this is not
// the case here).

[Num Of Reps With > 1 Prod Sold] =
SUMX(
    DISTINCT( 'Sales Rep'[RepID] ),
    // For each RepID it returns 1
    // if the representative sold more
    // than 1 ProdID in the current context.
    // Otherwise, it returns 0.
    CALCULATE(
        INT( DISTINCTCOUNT( 'Sales'[ProdID] ) > 1 )
    )
)
Payeras_BI
Super User I
Super User I

Hi @adris-misra ,

Payeras_BI_0-1614119409941.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

@Payeras_BI 

 

You should not use the SUMMARIZE function to do anything more than just group rows. This function has a fatal flaw and sometimes can return totally bogus, unexpected results. For more info on this unfixable bug, please find the article about SUMMARIZE and its quirks on www.sqlbi.com.

 

I strongly advise against using this function in measures if you put expressions under it. Instead, one should always use the combination SUMMARIZE/ADDCOLUMNS as suggested by Alberto Ferrari and Marco Russo.

 

Thanks.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors