Forum Discussion
Categorising data into custom categories
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 ...
v-frfei-msft
Community Support
Community SupportHi 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
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