Counting Rows

[Pmorg73]

I am working on counting a number of jobs where the client is repeated. So if a client has repeat work with us I get a total number of jobs for that client. In the screen grab. I have worked out the numbers using  Number of jobs = countrows('02_Job List')

I then made a table and it works out the number 277 for me by adding a filter to the visual of "number of jobs>1". I want to do this as a graphic next. How do I calculate this number using DAX  so that Ican compare the total number of jobs to the number of jobs of repeat clients? I will be doing the same with the count of repeat clients (50) and the totalnumber of clients (321)

Been playing with it for an hour and getting errors.

[v-lionel-msft]

Hi @Pmorg73 ,

Is here what you want?

// a measure
total_number of jobs = 
CALCULATE(
    DISTINCTCOUNT('table (2)'[job id]),
    ALLSELECTED('table (2)')
)

//a measure
A = 
CALCULATE(
    DISTINCTCOUNT('table (2)'[job id]),
    FILTER(
        ALLSELECTED('table (2)'),
        'table (2)'[client id] = MAX('table (2)'[client id])
    )
)
//a measure
Total number of jobs of repeat repeat = 
IF(
    'table (2)'[A] >1,
    'table (2)'[A]
)

Subtraction calculation = 
'table (2)'[total_number of jobs]- 'table (2)'[Total number of jobs of repeat repeat]

Best Regards,

Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pmorg73]

Hi

Its gotten me a long way forward but its not quie right. Each job ID only has one client id. The number of jobs of epeat clients expression below works out individual client numbers. How would I sum this? sum does not allow me to select it from the list.

Number of jobs of repeat clients =

IF(

'02_Job List'[Repeat Client calc] >1,

'02_Job List'[Repeat Client calc]

)

[Pmorg73]

[v-lionel-msft · Accepted solution]

Hi @Pmorg73 ,
My original data table:

//measure
total number of jobs = 
CALCULATE(
    DISTINCTCOUNT(Sheet9[Job_id]),
    ALLSELECTED(Sheet9)
) 

total number of clients = CALCULATE(
    DISTINCTCOUNT(Sheet9[Client_id]),
    ALLSELECTED(Sheet9)
) 

//column
repeat number for every client = 
CALCULATE(
    COUNT(Sheet9[Client_id]),
    ALLEXCEPT(
        Sheet9,
        Sheet9[Client_id]
    )
)

//measure
number of repeat clients = 
CALCULATE(
    COUNT(Sheet9[Client_id]),
    FILTER(
        Sheet9,
        Sheet9[repeat number for every client] >1
    ))

total number of jobs of repeat clients = 
CALCULATE(
    COUNT(Sheet9[Job_id]),
    FILTER(
        Sheet9,
        Sheet9[repeat number for every client] >1
    )
)

Best regards,
Lionel Chen

[Pmorg73]

This is good and has improved what I was doing. But your number of repeat clients is wrong. It equals the number of repeat jobs and is not the number of clients that have repeated.

In your example it would be 4 ( A to D).

Job Id as well repeats in your example where as I only ever have a single entry, so your example would be 1 to 12. 

I altered for Distinctcounts  and I think it wokrs now

Thanks for your help so far

[Pmorg73]

This is good and has improved what I was doing. But your number of repeat clients is wrong. It equals the number of repeat jobs and is not the number of clients that have repeated.

In your example it would be 4 ( A to D).

Job Id as well repeats in your example where as I only ever have a single entry, so your example would be 1 to 12. 

Thanks for your help so far