Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
TJCappy
Frequent Visitor

Calculating variance from system average

I have the following Data Tables with the respective columns

  • JTitle
    1. JobTitle1
    2. JobCode
  •  TeamCodes
    1. TeamCode
    2. Team Name
    3. Department
    4. Division
    5. Group
  • Calendar Table
    1. Date
    2. Month
    3. Day
    4. Year
    5. Weeknumber
  • EmplyeeDir
    1. Date
    2. UID-User
    3. UID-Emp
    4. UID-eml
    5. EmplNm
    6. JobTitle1
    7. ModifiedJT
    8. TeamCode
    9. TeamName
    10. EmpleeNumber
  • TData
    1. TxnType
    2. TxnDiscription
    3. FTTransactionType
    4. TxnDate
    5. UID-user
    6. Class

 

I then have the following measures

  1. to count the number of employees of team 4212

Count Tm4212 Employees =
CALCULATE (
    DISTINCTCOUNT ( 'TData'[EmployeeNumber] ),
    'Team Codes'[Team Code] = 4212
)

  1. 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"
)

 

  1. 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 )

 

 

2 REPLIES 2
Anonymous
Not applicable

Hi @TJCappy ,

 

Try removing ALL and use allselected if you have a filter. please provide your sample data in tabular form.

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

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

Hi @Anonymous 

thanks for the response, unfortunately, it stil does not calclate correctly.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.