Forum Discussion
Cross referencing with in the same table
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.)
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
3 Replies
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