How do i assign a value to a letter

[stevemc422]

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!

[MFelix · Accepted 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

[stevemc422]

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!