PowerPivot Unique CountIfs

[eliwaite]

I have two tables, similar to below:

Roster:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]Jon[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]Nancy[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]Adam[/TD]
[/TR]
</tbody>[/TABLE]

Sales:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Name[/TD]
[TD]Sales Amount[/TD]
[TD]Has Sale[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]Jon[/TD]
[TD]$100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]Nancy[/TD]
[TD]$200[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]Nancy[/TD]
[TD]$50[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]Jon[/TD]
[TD]$100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]Jon[/TD]
[TD]$150[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

The two tables are connected by the Name column. I am trying to determine what percentage of sales reps have sold something. I would like the output table to look like:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Region/Name[/TD]
[TD]% with Sales[/TD]
[/TR]
[TR]
[TD]AMS[/TD]
[TD]66%[/TD]
[/TR]
[TR]
[TD] Jon[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD] Nancy[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD] Adam[/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]

The basic formula is "Has Sale"/"Count of Names"

I got the "Count of Names", what I need help with is how to get an accurate "Has Sale".

I have tried: Calculate(countrows('Sales'),'Sales'[Has Sale]=1), but this returns 2 for Nancy, 3 for Jon and 5 for AMS. What I need is a formula that will return 1 for Nancy, 1 for Jon and 2 for AMS. I am assuming there is probably a straightforward solution for this, but being new to PowerPivot and DAX formulas I am at a loss. Any advice would be greatly appreciated.

 

[ImkeF]

Try this: CALCULATE(AVERAGE([Has Sale])*DISTINCTCOUNT([Name]);Sales[Has Sale]=1)

hth, Imke

 

[eliwaite]

Thanks for the advice. I ended up going with: DIVIDE(CALCULATE(DISTINCTCOUNT(Roster[Name]),FILTER('Sales','Sales'[Has Sale]>0)),[Count of Employees]) I had to use the >0 because my real table has sales for different products, and I only wanted the results for a specific product.