Count number of unique items

[laxmikantpatil]

I have two tables as below

Name  Target

a            3

b            2  

c            1

d            2

Name Job Status

a           x      Complete

a           y     Incomplete

b          z       Incomplete

a          w      Incomplete

b          u       Complete

c          o        Complete

d          p        Incomplete

d          l         Complete

Now I want a new column in table to count number of completed jobs by a person and in the second column no. of incomplete jobs. (Just the count) So that I can see what percent of job is completed.

Please help.

Thank You

[shawne · Accepted solution]

@laxmikantpatil Try this: 

[Anonymous]

@laxmikantpatil  - to get the % you can add this measure:

Percent Complete = 
DIVIDE(
    CALCULATE(COUNTROWS(YourTable), YourTable[Status] = "Complete"),
    COUNTROWS(YourTable)
)

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[laxmikantpatil]

Hi it doesn't give me the exact count corresponding to the right name.

[shawne · Accepted solution]

@laxmikantpatil Try this: 

[laxmikantpatil]

I am getting this warning

[shawne]

Can you share your file? Or at least an image of your data model? Hard to debug as I dont know your model or data set

[shawne · Accepted solution]

Without more details of your model or data I would guess you have a typo in your table and column reference.  Let the intellsense complete the phrase when referencing the needed column to be sure it is correct rather than typing it.  

here is the demo file I made for your example in the orig post.  Not getting the same error you show... 

https://www.dropbox.com/s/z2ghfczksfxzd1l/SolveCompletedDIstinct.pbix?dl=0

[laxmikantpatil]

Great Thank You

It worked

Thanks a million

[laxmikantpatil]

Yes this works.

Can you tell me how did you calculate no. of completed jobs.

I have calculated but it took 5 steps of editing.

Please let me know if there is a simple way.

[shawne]

See the bottom of the image....

Completed Jobs = CALCULATE(DISTINCTCOUNT(Results[Job]),Results[Status]="Complete")

[shawne]

PS - please mark this as solved if the solution is helpful! 

Cheers! 

[laxmikantpatil]

I have two different data tables.

Will this be valid for two different data sets too?