Looking up Score Values in another table

[ecky12]

I have two tables. AllGrades and GradeMapping

AllGrades has scores values of many users who took a survey. The one I care about is AllGrades.Score

In the other table (GradeMapping) I have a list of Grades associated with a range of scores (GradeMapping.LowScore,GradeMapping.HighScore,GradeMapping.LetterGrade)

I want to calculate the average value of the scores in AllGrades.score (which I am already displaying in my report) and lookup that value in GradeMapping so I can display the associated letter grade on my report.

Can someone help me with that? I am having all sorts of problems...and I pretty sure this should be simple.

[v-frfei-msft]

Hi @ecky12,

I made one sample for your reference, if it doesn't meet your requirement, kindly share your sample data and excepted result to me.

1. Enter the sample data and create two measures as below.

Ave = AVERAGEX(ALL(Table1),Table1[Score])

Measure = 
VAR LowScore =
    CALCULATE (
        MAX ( Table2[Score] ),
        FILTER ( Table2, Table2[GradeMapping] = "LowScore" )
    )
VAR HighScore =
    CALCULATE (
        MAX ( Table2[Score] ),
        FILTER ( Table2, Table2[GradeMapping] = "HighScore" )
    )
RETURN
    IF (
        [Ave] > HighScore,
        "HighScore",
        IF ( [Ave] > LowScore && [Ave] < HighScore, "LowScore", "N/A" )
    )

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @ecky12,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[Anonymous]

Can you please post some sample data with expected result set? That will be easiier to understand.

Thanks
Raj