unique count

[kin1]

i'm trying unique count from a column of data. each unique record will count as one. the image i pasted shows 2 of everything. does anyone know if i cant just count the same value as one? the same value can be listed more than 2 times, sometimes it can be as much as 20.

thanks

 

[scottsen]

Your image paste went... badly

 

[kin1]

Your image paste went... badly

ha,

ok.. ill try it here. i want to only count unique numbers that repeat. the list below should generate a total count of 3. can this be done in powerpivot?

thanks

numbers	count
1234	1
1234	0
5698	1
5698	0
abc	1
abc	0
total	3

<tbody>
</tbody>

 

[scottsen]

=DISTINCTCOUNT(Table1[Numbers])

 

[kin1]

i think when i tried that it gave me the total unique count, so instead of the 0 and 1's all the rows showed like 5011 which were the total unique value. the data is from an imported excel file, not sure how i can get the table?

 

[scottsen]

Sounds like you put it in a calculated column. Put it in a measure (calculated field).

 

[kin1]

ok that worked.. thank you!

 

[kin1]

hi scott, because there are multiple dates that can be associated to the job number, the numbers count shows up in multiple months. is there a way fix this problem? the grand total looks right but the the monthly sub total are incorrect. please let me know if there's a way around this, thanks!

 

[scottsen]

I'm not quite understanding your scenario, can you paste in some sample data (or a link to your workbook on google drive or similar)?

 

[kin1]

here's an example. ill use the / as a separate cell.

job# / Date
bmpk/ 08/01/2014
bmpk/ 09/15/2014
bmpk / 11/15/2014

if i use the distintcount([job#]) the grand total will be 1.

but on the monthly subtotal if i group the dates by month. it will show 1 in august, September and in November. which would be wrong if i want to get the correct month total, it should only appear as 1 count in august. can that be done?

thanks