Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |