Categorising data into custom categories

[Anonymous]

Hallo Everyone

I have Data, of which I will post a pic at end, which is questions asked to workers, divided into departments. There are 18 questions asked to a worker in a department and Q1-6 is cognative, 7-12 is physical and 13-18 is emotional. I need to determine which workers perform above, below and average. The basis is like this, if the difference between a workers rating and the average rating falls above the standard deviation range the worker is considered above average, below that range is below average and inside that range is average.

Now usually I would create a custom table, containing a column with the categories AA, A, BA with a maximum and minimum value inidcating the range for that specific category, and then with a measure one can determine the rating for individuals who fall inside the min and max for each category. My problem is, the average and standard deviation differ over departments and also over the 3 sections of the Q.

How would one determine a dynamical result, meaning, if I select a department or/and one of the sections of the questions and/or AA, A, BA then it must show me the rating and average rating for those individuals. 

See below a pic of my data.

[v-frfei-msft]

Hi @Anonymous,

Here I made a sampe, You can refer to the following steps.

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

avd = IF(ISFILTERED(Table1[Department]),CALCULATE(SUM(Table1[Rating]),ALLSELECTED(Table1))/CALCULATE(DISTINCTCOUNT(Table1[Department]),ALLSELECTED(Table1)))

AVQ = IF(ISFILTERED(Table1[Questions]),CALCULATE(SUM(Table1[Rating]),ALLSELECTED(Table1))/CALCULATE(COUNTROWS(Table1),ALLSELECTED(Table1)))

SUMRANT = CALCULATE(SUM(Table1[Rating]),ALLEXCEPT(Table1,Table1[Worker]))

AVERAGE = IF(ISFILTERED(Table2[categories]) &&[SUMRANT]<=MAX(Table2[MAX]) && [SUMRANT]>=MIN(Table2[MIN]),CALCULATE(SUM(Table1[Rating]),ALLSELECTED(Table1))/CALCULATE(DISTINCTCOUNT(Table1[Worker]),ALLSELECTED(Table1)),0)

2.Filter the table visual make the AVERAGE is not 0. Then we can get the result as below.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/fyeoiltabdej2jl/Categorising%20data%20into%20custom%20categories.pbix?dl=0

If it doesn't meet your requirement, kindly share your sample data and excepted result to me.

Regards,

Frank

[Anonymous]

Hallo

Thank you very much for the reply. I apologise for letting you wait this long. I actually found a solution that worked very well. Will post later today to show you. Will also try yours in the mean time and let you know

Thank you very much again

Regards

[v-frfei-msft]

Hi @Anonymous,

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

Regards,

Frank