Categorising data into custom categories

Question

Hallo Everyone&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;See below a pic of my data.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-frfei-msft · Answer

Hi&nbsp;Anonymous,&nbsp;Here I made a sampe, You can refer to the following steps.&nbsp;1.Enter the data and create the measures as below.&nbsp;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]) &amp;&amp;[SUMRANT]&lt;=MAX(Table2[MAX]) &amp;&amp; [SUMRANT]&gt;=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.&nbsp;&nbsp;For more details, please check the pbix as attached.&nbsp;https://www.dropbox.com/s/fyeoiltabdej2jl/Categorising%20data%20into%20custom%20categories.pbix?dl=0&nbsp;If it doesn't meet your requirement, kindly share your sample data and excepted result to me.&nbsp;Regards,Frank

v-frfei-msft · Answer

Hi&nbsp;Anonymous,&nbsp;Does that make sense? If so, kindly mark my answer as a solution to close the case.&nbsp;Regards,Frank

anonymous · Answer

Hallo&nbsp;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&nbsp;Thank you very much again&nbsp;Regards

Forum Discussion

Related content

Categorise average percentages

Total is wrong when categorised is vendor wise

Categorise Plus or Minus %

Average of categories over vendor

Custom Visual