I'm trying to calculate a distinct count of customers and having some difficulty; my table is like this,
<tbody>
</tbody>
So what I am looking for is the distinct count of Sales Team by Customer, but I want the measure to respect any filters applied to Col1, Col2, Col3.
So if I have a PivotTable showing customer abc with no other filters, then it should show 2. But if I have a slicer that is slicing Col1's value of 'yy' out, then it should be just 1.
So far I have:
But this would still return 2 even if I filter out 'yy'.
Is there an easy solution?
Thanks
Customer | Sales Team | Col1 | Col2 | Col3 |
abc | 123 | xx | xx | xx |
def | 456 | xx | xx | xx |
abc | 789 | yy | yy | yy |
<tbody>
</tbody>
So what I am looking for is the distinct count of Sales Team by Customer, but I want the measure to respect any filters applied to Col1, Col2, Col3.
So if I have a PivotTable showing customer abc with no other filters, then it should show 2. But if I have a slicer that is slicing Col1's value of 'yy' out, then it should be just 1.
So far I have:
Code:
=
[SIZE=1][COLOR=#0000ff][SIZE=1][COLOR=#0000ff]CALCULATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]([/SIZE][SIZE=1][COLOR=#0000ff][SIZE=1][COLOR=#0000ff]DISTINCTCOUNT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]( [Sales Team] ),[/SIZE][SIZE=1][COLOR=#0000ff][SIZE=1][COLOR=#0000ff]ALLEXCEPT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Sales,Sales[Customer]))[/SIZE]
[SIZE=1][/SIZE]
But this would still return 2 even if I filter out 'yy'.
Is there an easy solution?
Thanks