Counting a Count?

[Michael_Hart]

Hi all, 

I have a set of data that contains the location of items in a warehouse. As a simplified version:

Location	Item no.
A	001
B	002
A	003
A	004
C	005
D	007
C	008

I can then count the number of times a location is used.

Location	count(location)
A	3
B	1
C	2
D	1

I then want a KPI that shows the number of locations that only have 1 item. 

i.e 

2 locations have 1 item

How can I do this in one formula.

Thanks for the help.

[joseph_morales · Accepted solution]

Hi @Michael_Hart 

You can try this:

Sum (

if( Aggr( Count([Item no.]) , Location ) = 1 , 1, 0 )

)

Regards

[RsQK · Accepted solution]

Hey, you can get the result like this:

COUNT({<Location={"=COUNT(Location)=1"}>}Location)

[Michael_Hart]

Thank you both, either solution seemed to get the answer I was looking for!

Much appreciated.