Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
While using a matrix chart
I am currently trying find the average of a rating but haivng trouble transfering the number assoisated back to a letter.
example:
A =1
B =2
C =3
If i wanted to find the average of:
A
A
B
C
AVERAGE = 1.75 = B
MY PROBLEM IS when transfer them to numbers. Then get the average, but i am unable to turn the average BACK into the letter rating in Power BI.
Has anyone done anything like this? I am only able to get the first letter rating not the letter that would be the correct average.
Thanks!
Solved! Go to Solution.
Hi @stevemc422,
I did a table with Grade and Values so basically
A - 1
B - 2
C - 3
D - 4
E - 5
Then added a column to the students classification with the following formula:
Grade_Value = LOOKUPVALUE(Grades[Value],Grades[Grade],Students[Grade])
Added the following measure:
Average_Grades = VAR Average_Numbers = CALCULATE ( AVERAGE ( Students[Grade_Value] ) ) RETURN LOOKUPVALUE ( Grades[Grade], Grades[Value], ROUNDDOWN ( Average_Numbers, 0 ) )
Final result is a table or a card with average in Letters not numbers, you can change the rounddown to roundup in order to achieve the expect result in the table grades:
As you can see you get the Grade based on any grouping you have in the lowest matrix I did two group A&B / C&D / E (this was done on the Grades table) and the final result gives you A for first roup and C for the second group
(A + B + A + B + A) / 5 = (1 + 2 + 1 + 2 +1) / 5 = 7 / 5 = 1,4 => Rounddown = 1 = A
(C + D + D + D + C ) / 5 = (3 + 4 + 4 + 4 + 3 ) / 5 = 18 / 5 = 3,6 => Roundown = 3 = C
Not sure if it fits your model but can be adjusted.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @stevemc422,
I did a table with Grade and Values so basically
A - 1
B - 2
C - 3
D - 4
E - 5
Then added a column to the students classification with the following formula:
Grade_Value = LOOKUPVALUE(Grades[Value],Grades[Grade],Students[Grade])
Added the following measure:
Average_Grades = VAR Average_Numbers = CALCULATE ( AVERAGE ( Students[Grade_Value] ) ) RETURN LOOKUPVALUE ( Grades[Grade], Grades[Value], ROUNDDOWN ( Average_Numbers, 0 ) )
Final result is a table or a card with average in Letters not numbers, you can change the rounddown to roundup in order to achieve the expect result in the table grades:
As you can see you get the Grade based on any grouping you have in the lowest matrix I did two group A&B / C&D / E (this was done on the Grades table) and the final result gives you A for first roup and C for the second group
(A + B + A + B + A) / 5 = (1 + 2 + 1 + 2 +1) / 5 = 7 / 5 = 1,4 => Rounddown = 1 = A
(C + D + D + D + C ) / 5 = (3 + 4 + 4 + 4 + 3 ) / 5 = 18 / 5 = 3,6 => Roundown = 3 = C
Not sure if it fits your model but can be adjusted.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello!
While using a matrix chart
I am currently trying find the average of a rating but haivng trouble transfering the number assoisated back to a letter.
example:
A =1
B =2
C =3
If i wanted to find the average of:
A
A
B
C
AVERAGE = 1.75 = B
MY PROBLEM IS when transfer them to numbers. Then get the average, but i am unable to turn the average BACK into the letter rating in Power BI.
Has anyone done anything like this? I am only able to get the first letter rating not the letter that would be the correct average.
Thanks!
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |