Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table that shows the evaluation results (results range from 1 to 10) of 2 diferent skills in an organization.
I need to get the average result per employee and then group those results with the following outcome criteria:
< 3.4 = Bad
< 6.7 = Ok
< 9 = Good
<= 10 = Excellent
To do so, I need a table with the average result per employee and the outcome per average result:
Then, I just count each outcome, getting the desired result table as follows:
The problem is that this result should also be affected by Slicers for the [Area] and the [Skill] filters. So, for example if I filter by [Skill]="Fast" and [Area]="Talent Care", the result table should actually be the following:
This is a small sample of a much larger file with several Areas and Skills evaluated, and I would like to know what percentage of people did good, ok or bad on average, either with or without filters. What I described is the mental process that I use for the desired result table, but I don't know how to get it done, hopefully somebody can help me figure it out.
Solved! Go to Solution.
Hi,
I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file whether it suits your requirement.
Expected result measure: =
VAR _avgperemp =
ADDCOLUMNS (
DISTINCT ( Data[Employee] ),
"@avg", CALCULATE ( AVERAGEX ( Data, Data[Result] ) )
)
RETURN
IF (
HASONEVALUE ( Outcome[Outcome] ),
COUNTROWS (
FILTER (
_avgperemp,
[@avg] >= MAX ( Outcome[Min] )
&& [@avg] <= MAX ( Outcome[Max] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kimthis is exciting, my first ever post and my question was beautifully answered. Exactly what I needed.
Thank you so much!!
Hi,
I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file whether it suits your requirement.
Expected result measure: =
VAR _avgperemp =
ADDCOLUMNS (
DISTINCT ( Data[Employee] ),
"@avg", CALCULATE ( AVERAGEX ( Data, Data[Result] ) )
)
RETURN
IF (
HASONEVALUE ( Outcome[Outcome] ),
COUNTROWS (
FILTER (
_avgperemp,
[@avg] >= MAX ( Outcome[Min] )
&& [@avg] <= MAX ( Outcome[Max] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |