Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
I have the following Data Tables with the respective columns
I then have the following measures
Count Tm4212 Employees =
CALCULATE (
DISTINCTCOUNT ( 'TData'[EmployeeNumber] ),
'Team Codes'[Team Code] = 4212
)
TData Count FXOst Txn =
CALCULATE (
[Tdata Count all Atwt Txn Type],
ALL ( 'TData'[FTTransactionType] ),
'TData'[FTTransactionType] = "Atwt"
)
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.
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 )
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |