Cross referencing with in the same table

[Anonymous]

Hello,

I have a data set that references the employees in two columns. But I need to compare them. When I conduct a count formula, to distinguish the number of cases assigned by an employee and the number of cases scheduled by an employee, the numbers are always identical. Here is what the data looks like. How do count how many ID's have been scheduled by an employee and how many ID's have been assigned by an employee? 

#Assigned by = COUNT('Interpreter usage report'[Assigned by.1])

#Scheduledby =

COUNT('Interpreter usage report'[Scheduled by.1])

When I make a list of employees and add these measures as columns. The numbers are inaccurate. 

Is there a better way to organize this data? Also, I will be adding other measures to the table (i.e. Average rate per employee, etc.)

[Anonymous · Accepted solution]

HI @Anonymous,

You can try to use the following measure formula to calculate the field aggregated based on current employee group:

#Assigned by =
CALCULATE (
    COUNT ( 'Interpreter usage report'[Assigned by.1] ),
    ALLSELECTED ( 'Interpreter usage report' ),
    VALUES ( 'Interpreter usage report'[Employee] )
)

#Scheduledby =
CALCULATE (
    COUNT ( 'Interpreter usage report'[Scheduled by.1] ),
    ALLSELECTED ( 'Interpreter usage report' ),
    VALUES ( 'Interpreter usage report'[Employee] )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Sorry for the delay, but that did not resolve my issue. The numbers are inaccurate. 

[Anonymous]

Here is what the table looks like when I try to put the measures in the same visual...