Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Basically, I want to build a graph that shows the % of employees in our business unit that have received 2 coaching sessions per month. I created a measure:
Filter Measure = SWITCH(TRUE(), COUNT(Merge1[Master Roster.Rep ID]) >= 2, 1, COUNT(Merge1[Master Roster.Rep ID]) = 1, .50, COUNT(Merge1[Master Roster.Rep ID]) < 1, 0)
Which looks correct on this table:
In July, filtering for those 3 employee ID numbers, U34826 did receive 2 coaching sessions, so he/she met the goal for that month (100%). Employee U35034 did not receive any coaching for that month, so he/she is 0%. Employee U37046 only received 1 coaching that month, so he/she is 50%.
Where I'm stuck now is, I want to show the average of these employees percentages for each month but can't figure out how to do so since the average calculation:
Measure2 = AVERAGEX(VALUES(Merge1[Master Roster.Rep ID]), [Filter Measure])
is returning these values:
And calculating the average total for July 2020 as 75%, but it should actually be 50% since the employee who did not receive any coaching needs to be factored into the calculation.
The correct logic I'm hoping for would be (for July 2020):
(100% + 0% + 50%) / 3 distinct employees = 50%, not 75%
It's only taking 2 distinct employees and ignoring the employee with a 0%. How can I solve this?
Solved! Go to Solution.
Hi @ninos-shiba ,
Please refer to my .pbix file.
My fact table may be different from yours, but the calculation formula should be similar.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |