Calculating an average of scores whilst removing an outlier group of scores

[Dnicetin]

Hi

I am wondering if anyone had some ideas on how I can solve this problem using a Dax formula (or a combination of Dax Formula's).

I get student satisfaction scores broken out into Classes and Instructor’s
Currently I look at the class average of the instructor as a metric

What I would like to do is look at the class average but remove the lowest scoring class from this metric

See attached sample data
e.g 1
Instructor 1 has 3 classes that have the following averages.
Class Number 4 with a class Average of 5.00
Class Number 3 with a class Average of 4.65
Class number 5 with a class Average of 4.86
I would like the calculation to only take into account the 5.00 and 4.86 Scores when calculating the Average

Eg2
Instructor 2 has 3 classes that have the following averages.
Class number 6 with a class Average of 5.0
Class Number 2 with a class Average of 4.38
Class number 1 with a class Average of 4.50
I would like the calculation to only take into account the 5.00 and 4.50 Scores when calculating the Average

Student Name	instructor	Class Identifier Number	Satisfaction Score out of 5
Student #1	Instructor 1	Class Number 4	5
Student #2	Instructor 1	Class Number 4	5
Student #3	Instructor 1	Class Number 4	5
Student #4	Instructor 1	Class Number 4	5
Student #5	Instructor 1	Class Number 3	5
Student #6	Instructor 1	Class Number 3	5
Student #7	Instructor 1	Class Number 3	5
Student #8	Instructor 1	Class Number 3	3
Student #9	Instructor 1	Class Number 3	5
Student #10	Instructor 1	Class Number 3	4
Student #11	Instructor 1	Class Number 3	4
Student #12	Instructor 1	Class Number 3	5
Student #13	Instructor 1	Class Number 3	4
Student #14	Instructor 1	Class Number 3	5
Student #15	Instructor 1	Class Number 3	5
Student #16	Instructor 1	Class Number 3	5
Student #17	Instructor 1	Class Number 3	5
Student #18	Instructor 1	Class Number 3	4
Student #19	Instructor 1	Class Number 3	5
Student #20	Instructor 1	Class Number 3	5
Student #21	Instructor 1	Class Number 3	5
Student #22	Instructor 1	Class number 5	5
Student #23	Instructor 1	Class number 5	5
Student #24	Instructor 1	Class number 5	5
Student #25	Instructor 1	Class number 5	5
Student #26	Instructor 1	Class number 5	5
Student #27	Instructor 1	Class number 5	4
Student #28	Instructor 1	Class number 5	5
Student #29	Instructor 2	Class number 6	5
Student #30	Instructor 2	Class number 6	5
Student #31	Instructor 2	Class number 6	5
Student #32	Instructor 2	Class number 6	5
Student #33	Instructor 2	Class Number 2	5
Student #34	Instructor 2	Class Number 2	5
Student #35	Instructor 2	Class Number 2	3
Student #36	Instructor 2	Class Number 2	5
Student #37	Instructor 2	Class Number 2	5
Student #38	Instructor 2	Class Number 2	3
Student #39	Instructor 2	Class Number 2	5
Student #40	Instructor 2	Class Number 2	4
Student #41	Instructor 2	Class number 1	2
Student #42	Instructor 2	Class number 1	5
Student #43	Instructor 2	Class number 1	5
Student #44	Instructor 2	Class number 1	3
Student #45	Instructor 2	Class number 1	5
Student #46	Instructor 2	Class number 1	6
Student #47	Instructor 2	Class number 1	5
Student #48	Instructor 2	Class number 1	5

[ThxAlot]

A calculated table significantly simply the calculation.

[wdx223_Daniel]

AverageScore = AVERAGEX(VALUES(SampleTable[Class Identifier Number]),CALCULATE(AVERAGE(SampleTable[Satisfaction Score out of 5])))

AverageScoreRemoveLast1 = IF(COUNTROWS(VALUES(SampleTable[Class Identifier Number]))=1,[AverageScore],AVERAGEX(WINDOW(1,ABS,-2,ABS,VALUES('SampleTable'[Class Identifier Number]),ORDERBY([AverageScore],DESC)),[AverageScore]))

[Dnicetin]

thanks again for your work on this...

this looks like its coming together. but it stops at the (WINDOW section in the formula  😞

[FreemanZ]

hi @Dnicetin ,

try to plot a table visual with [instructor] column and a measure like:

AvgTop2 = 
VAR _table =
    ADDCOLUMNS(
        SUMMARIZE(data, data[instructor], data[Class]),
        "Avg",
        CALCULATE(AVERAGE(data[Score]))
    )
VAR result = 
    CALCULATE(
        AVERAGEX(
            TOPN(2, _table, [Avg]),
            [Avg]
        )
    )
RETURN result

it worked like:

[Dnicetin]

thanks for that.

I guess my challenge is its not always just three classes (sorry i had a bad example)

The number of classes is variable

[FreemanZ]

the proposed measure will always average on the top2, no matter how many classes each instructor has. just try with your original data and let us know the result. 

[Dnicetin]

i dont understand that format sorry. Just know how to create Measures on all my sheet's.

I have tried to convert it but its not liking the "VAR" onwards part (as i suspect its telling it to create something it doesnt undersand)

[FreemanZ]

the code is also good for measures used in Excel Pivot table.