topic Re: Calculating an average of scores whilst removing an outlier group of scores in DAX Commands and Tips

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

Dnicetin — Tue, 31 Oct 2023 00:58:18 GMT

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

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

FreemanZ — Tue, 31 Oct 2023 01:40:13 GMT

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:

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

Dnicetin — Tue, 31 Oct 2023 03:11:01 GMT

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

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

FreemanZ — Tue, 31 Oct 2023 03:18:48 GMT

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.

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

Dnicetin — Tue, 31 Oct 2023 03:57:34 GMT

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)

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

FreemanZ — Tue, 31 Oct 2023 06:18:52 GMT

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

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

wdx223_Daniel — Tue, 31 Oct 2023 06:22:35 GMT

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]))

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

ThxAlot — Tue, 31 Oct 2023 06:44:37 GMT

A calculated table significantly simply the calculation.

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

Dnicetin — Wed, 01 Nov 2023 00:40:24 GMT

thanks again for your work on this...

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