Count Unique Occurrences PowerPivot

Question

I am new to PowerPivot and DAX formulas. I assume that what I am trying to do is very basic and it has probably been answered somewhere, I just don't know what to search on it find it.

I am trying to determine the percent of sales people who had a sale in a given quarters. I have two tables, one that lists the sales people and one that list all the sales for a quarter. For example

Employee ID
123
456
789

Sales ID - Emp ID - Amount
135645 ---- 123 ----- $50
876531 ---- 123 ----- $127
258546 ---- 123 ----- $37
516589 ---- 789 ----- $128
998513 ---- 789 ----- $79

As a result, the pivot table would look like this:

Emp ID - % w/ sales
123 -------- 100%
456 -------- 0%
789 -------- 100%
Total ------- 66%

If you can point me to a post where this has been addressed or let me know the best way to address this I would appreciate it. Thank you.

Answer 1

Here's a simple way of doing this (assuming table names emps and sales):

=IF (DISTINCTCOUNT ( sales[Emp ID] ) = BLANK (),
        0,
        DISTINCTCOUNT ( sales[Emp ID] )
     )
    / COUNTROWS ( emps )

The IF() is only required to ensure that people who haven't made a sale appear in the Pivot. All the actual formula is doing is dividing the number of sales rows by the number of employee rows.

Jacob

Answer 2

You'll need to remove the text that begins with --. I wanted to describe what the DAX is doing. This may not do what you want because it only factors the employees in the context. E.x.: If the user filtered out all employees that didn't have sales, should the grand total be 100% or 66%? For the former, you'll need to use the ALL DAX function and the below DAX does the latter. I'm very new to DAX so I'm sure there's a better way to do what you want.

=IF
(
-- are we processing 1 employee or multiple employees? (E.x.: The grand total processes multiple employees...)
COUNTROWS(VALUES(employee[Employee ID])) > 1, 


--If Processing multiple employees do X / Y
-- Where X = The number of employees that have sales
-- Where Y = The number of employees selected by the user
COUNTROWS(FILTER(employee, NOT(ISBLANK(CALCULATE(COUNT(sales[Sales ID])))))) / COUNTROWS(employee),


-- If processing single employee, return 0 if they have no sales, and 1 if they have sales
IF
	(
	ISBLANK(CALCULATE(COUNT(sales[Sales ID]))),
	0,
	1
	)
)