Calculating variance from system average
I have the following Data Tables with the respective columns JTitle JobTitle1 JobCode TeamCodes TeamCode Team Name Department Division Group Calendar Table Date Month Day Year Weeknumber EmplyeeDir Date UID-User UID-Emp UID-eml EmplNm JobTitle1 ModifiedJT TeamCode TeamName EmpleeNumber TData TxnType TxnDiscription FTTransactionType TxnDate UID-user Class I then have the following measures to count the number of employees of team 4212 Count Tm4212 Employees = CALCULATE ( DISTINCTCOUNT ( 'TData'[EmployeeNumber] ), 'Team Codes'[Team Code] = 4212 ) to count all transactions with an FTTranxaction Code of Atwt TData Count FXOst Txn = CALCULATE ( [Tdata Count all Atwt Txn Type], ALL ( 'TData'[FTTransactionType] ), 'TData'[FTTransactionType] = "Atwt" ) This to calculate the variance between a single selected employee and the system average (Avg of all the employees in team 4212) TData Variance FxOst = VAR varEmpFXOst = [Tdata Count FXOst Txn] VAR varSystemFXOst = CALCULATE ( [Tdata Count FXOst Txn], ALL ( 'T24 Data' ), 'TData'[FTTransactionType] = "Atwt" ) VAR varVariance = DIVIDE ( varSystemFXOst - varEmpFXOst, varSystemFXOst ) RETURN IF ( varEmpFXOst = BLANK (), BLANK (), varVariance ) The result for emplyee #236 is as follows: TData Count FXOst Txn = 57 the number of emplyees in team 4212 is 12 This gives me an average of 24.58 "Atwt" transactions which would mean that the Variance should be 1.32 meaning that that employee has done 132% more than the system average of 24.58. whoever, the result I am getting is 1 (100%) how do I get the correct result? EDIT I have now tried the following additional formulae and none seem to work. a) TData Variance FxOst = VAR varEmpFXOst = [Tdata Count FXOst Txn] VAR varSystemFXOst = AVERAGEX(FILTER('Employee Directory','EmployeeDir'[TeamCode] = 4212), [Tdata Count FXOst Txn]) VAR varVariance = DIVIDE(varEmpFXFXOst - varAvgSystemFXOst, varAvgSystemFXOst) RETURN IF( varEmpFXOst = BLANK(), BLANK(), varVariance ) b) Tdata Variance FxOst = var varEmpFXOst = [Tdata Count FXOst txn] var varSystemFXOst = AverageX(Filter('EmployeeDir','EmployeeDir'[TeamCode] = 4212), Calculate ([Tdata Count FXOst],'TData'[FTTransactionType]="Atwt") ) c) Tdata Variance FxOst = var varEmpFXOst = [Tdata Count FX Ost txn] var varSystemFXOst = AverageX(Filter('EmployeeDir','EmployeeDir'[TeamCode] = 4212), Calculate ([T24 Count FXOst Txn],'T24 Data'[FTTransactionType]="ACWF") ) var varVariance = DIVIDE( varEmpFXFee - varSystemFXFee, varSystemFXFee) return if(varEmpFXFee = Blank(),Blank(),varVariance ) var varVariance = DIVIDE(varSystemFXOst- varEmpFXOst , varSystemFXOst) return if(varEmpFXOst = Blank(),Blank(),varVariance ) TData Variance FxOst = VAR varEmpFXOst = [Tdata Count FXOst Txn] VAR varSystemFXOst = CALCULATE ( [Tdata Count FXOst Txn], ALLSelected ( 'T24 Data' ), 'TData'[FTTransactionType] = "Atwt" ) VAR varVariance = DIVIDE ( varSystemFXOst - varEmpFXOst, varSystemFXOst ) RETURN IF ( varEmpFXOst = BLANK (), BLANK (), varVariance )