Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone,
Requesting for your help in creating a Population Pass Rate computation wherein the target is dynamic. Please see sample data below:
ID | Employee Name | Supervisor | Units | Time | Unit Pass? |
160 | Aydin Samuels | Fern Bowden | 7258 | 59.3 | 1 |
138 | Lesley Rigby | Kyla Taylor | 7149 | 50.8 | 1 |
132 | Annette Hancock | Carrie Austin | 7042 | 57.8 | 1 |
196 | Safah Medina | Kyla Taylor | 7031 | 79.5 | 1 |
158 | Aqsa Yoder | Carrie Austin | 7030 | 49.3 | 1 |
140 | Elen Piper | Fern Bowden | 7026 | 44.8 | 1 |
124 | Inez Marks | Kyla Taylor | 6885 | 91.5 | 1 |
276 | Neve Walters | Fern Bowden | 6744 | 62.9 | 0 |
221 | Ayesha Kaur | Kyla Taylor | 6542 | 91.8 | 0 |
148 | Nathanael Goulding | Carrie Austin | 6510 | 82.9 | 0 |
181 | Tina Sherman | Fern Bowden | 6331 | 41.2 | 0 |
223 | Charlize Mitchell | Kyla Taylor | 6290 | 46.2 | 0 |
255 | Millie-Rose Rollins | Carrie Austin | 6070 | 37.8 | 0 |
My Expected output:
AVERAGE (UNIT TARGET) | 6762 |
Supervisor | Pass Count | Total Employee | Pass Rate |
Fern Bowden | 2 | 4 | 50.0% |
Kyla Taylor | 3 | 5 | 60.0% |
Carrie Austin | 2 | 4 | 50.0% |
In my Report, there is a date slicer wherein the total units of each employee changes. The target that I have for the units is based on the average of the population, so the target might always change
I have sucessfully created a measure to get the average of entire population & identify if the employee passed it or not but the problem comes in when I want to create a summary per supervisor to know:
The computation that I created to identify the employee pass it or not always shows 1 at the total. What I want to happen is to show the total # of people that pass it.
Below are the Measures that I created:
Target-Volume = CALCULATE(AVERAGEX(ALLSELECTED(EmployeeName), CALCULATE(SUM(Units))), ALLSELECTED(MyTable))
I hope I am in the right page, if not please let me know so that I can transfer it to the correct section.
Thank you so much and I appreciate the help or inputs that you might give.
Solved! Go to Solution.
Hi @krixtsup3r
Create measures
pass count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Employee Name] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Supervisor]
= MAX ( 'Table'[Supervisor] )
&& 'Table'[Unit Pass?] = 1
)
)
total emp =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Employee Name] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Supervisor]
= MAX ( 'Table'[Supervisor] )
)
)
pass rate = [pass count]/[total emp]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @krixtsup3r
Create measures
pass count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Employee Name] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Supervisor]
= MAX ( 'Table'[Supervisor] )
&& 'Table'[Unit Pass?] = 1
)
)
total emp =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Employee Name] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Supervisor]
= MAX ( 'Table'[Supervisor] )
)
)
pass rate = [pass count]/[total emp]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-juanli-msft,
Thank you. I think this is the right route but I have one more problem this:
'Table'[Unit Pass?]
is actually nonexistent to my table, I also need a formula that does this.
Thank you! I will try this one later and will let you know if it works.
Appreciate your help! 🙂
Hello @krixtsup3r,
I did not fully understand your issue, but are you facing issue calculating Pass rate?
If yes, please use:
Pass Rate =
VAR PassCount = CALCULATE(DISTINCTCOUNT(Table[ID]),Table[Unit Pass?]=1)
VAR TotalEmployees = DISTINCTCOUNT(Table[ID])
RETURN DIVIDE(PassCount,TotalEmployees)
If not, please guide ahead.
Hi,
Thank you for your prompt response and apologies if I wasn't able to explain it better.
What I really need is:
- A dynamic target that changes based on the selected date coverage. The target is the average of all the data per employee.
- Pass Count measure if the employee passes (value is 1 or 0), the total should also show how many passed
Lastly, a table that will summarize this by supervisor shown in the table above
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |