Distributed Averages

[cottrera]

Hi , I have the following table

Unit Reference	Job#
18	7712012
18	8029903
18	8322638
18	8322662
18	8452691
18	9302275
26	7689550
26	8691041
26	8700602
26	9250383
34	8251499
34	8251506
34	8252562
34	8471047
34	8532055
34	8536396
34	8633522
34	8633530
34	8645402
34	8817374
34	8821284
42	7545869
42	7545934
42	7545942
42	7856620
42	7900956
42	8090714
42	8582828
50	7036834
50	7207112
50	7289277
50	7302384

Which contains Unit references and Job numbers.   The units will normally have multiple jobs against them.  I am after a DAX function or a power query solution to score based on the number of repairs using the following criteria. 

Score	Calculation
5	Greater than twice above average
4	Between above average and twice above average
3	Average
2	Between Average and twice below average
1	Between zero and twice below average
1	Zero

thank you

Richard

[cottrera]

Hi based in this scaled down data set

Unit Reference	#Repairs = COUNTROWS(Repairs)
18	6
26	4
34	11
42	7
50	19
76	5
92	2
109	10
125	7
133	7
141	4
159	5
183	4
191	14
208	30
216	11
224	9
232	33
240	22
258	10
266
290	28
307	16
315	12
323	38
349	18
1660	6
1678	6
1686	19
1701	8
1719	9
1727	10
1735	6
1743	11
1751	5
1769	6
1777	3
1785	6
1793	3
1800	2
1818	1
1826	6
1834	10
1842	6
1850	6
1868	15
1876	4
1884	1
1892	10
1909	10
1917	18
1925	18
1933	23
1941	12
1959	12
1975	18
1983	9
1991	8
2014	15
2022	17
2030	3
2048	24
2064	54
2072	2
2105	13
2113	6
2121	5
2147	18
2163	40
2171	17
2189	2
2197	33
2204	3
2212	21
2220	10
2238	1
2303	29
2329	25
2337	11
2345	21
2387	19
2395	10
2402	13
2410	22
2436	5
2444	27
2452	19
2478	15
2486	9
2494	39
2501	37
2519	12
2527	5
2543
2551
2569	6
2577	1
2585	5
2593	5
2650	2
2668	10
2676	52
2684	8
2692	3
2717	2
2759	13
2767	23
2775	8
2783	9
2808	5
2973	14
2999	1
3004	16
3012	27
3020	4
3038	6
3046	46
3054	9
3062	3
3070	24
3088	56
3096	6
3103	6
3111	7
3129	24
3137	2
3145	3
3153	29
3161	4
3179	9
3187	22
3195	13
3228	16
3236	1
3244	4
3252	15
3260	7
3278	9
3294	34
3301	17
3327	5
3351	23
3369	15
3377	2
3385	11
3393	12
3400	1
3418	20
3426	10
3434	4
3442	3
3450	2
3468	1
3476	17
3484	13
3492	4
3509	10

 When added to the table I would expect the following results

Unit Reference	Repairs	Score Name	Score
18	6	Between 5 and average	2
26	4	Between above average and twice above average	4
34	11	Between 5 and average	2
42	7	Between 5 and average	2
50	19	Between above average and twice above average	4
76	5	Between zero and 5	1
92	2	Between zero and 5	1
109	10	Between 5 and average	2
125	7	Between 5 and average	2
133	7	Between 5 and average	2
141	4	Between zero and 5	1
159	5	Between zero and 5	1
183	4	Between zero and 5	1
191	14	Between above average and twice above average	4
208	30	Greater than twice above average	5
216	11	Between 5 and average	2
224	9	Between 5 and average	2
232	33	Greater than twice above average	5
240	22	Between above average and twice above average	4
258	10	Between 5 and average	2
266		Zero	1
290	28	Greater than twice above average	5
307	16	Between above average and twice above average	4
315	12	Between 5 and average	2
323	38	Greater than twice above average	5
349	18	Between above average and twice above average	4
1660	6	Between 5 and average	2
1678	6	Between 5 and average	2
1686	19	Between above average and twice above average	4
1701	8	Between 5 and average	2
1719	9	Between 5 and average	2
1727	10	Between 5 and average	2
1735	6	Between 5 and average	2
1743	11	Between 5 and average	2
1751	5	Between zero and 5	1
1769	6	Between 5 and average	2
1777	3	Between zero and 5	1
1785	6	Between 5 and average	2
1793	3	Between zero and 5	1
1800	2	Between zero and 5	1
1818	1	Between zero and 5	1
1826	6	Between 5 and average	2
1834	10	Between 5 and average	2
1842	6	Between 5 and average	2
1850	6	Between 5 and average	2
1868	15	Between above average and twice above average	4
1876	4	Between zero and 5	1
1884	1	Between zero and 5	1
1892	10	Between 5 and average	2
1909	10	Between 5 and average	2
1917	18	Between above average and twice above average	4
1925	18	Between above average and twice above average	4
1933	23	Between above average and twice above average	4
1941	12	Between 5 and average	2
1959	12	Between 5 and average	2
1975	18	Between above average and twice above average	4
1983	9	Between 5 and average	2
1991	8	Between 5 and average	2
2014	15	Between above average and twice above average	4
2022	17	Between above average and twice above average	4
2030	3	Between zero and 5	1
2048	24	Between above average and twice above average	4
2064	54	Greater than twice above average	5
2072	2	Between zero and 5	1
2105	13	Average	3
2113	6	Between 5 and average	2
2121	5	Between zero and 5	1
2147	18	Between above average and twice above average	4
2163	40	Greater than twice above average	5
2171	17	Between above average and twice above average	4
2189	2	Between zero and 5	1
2197	33	Greater than twice above average	5
2204	3	Between zero and 5	1
2212	21	Between above average and twice above average	4
2220	10	Between 5 and average	2
2238	1	Between zero and 5	1
2303	29	Greater than twice above average	5
2329	25	Between above average and twice above average	4
2337	11	Between 5 and average	2
2345	21	Between above average and twice above average	4
2387	19	Between above average and twice above average	4
2395	10	Between 5 and average	2
2402	13	Average	3
2410	22	Between above average and twice above average	4
2436	5	Between zero and 5	1
2444	27	Greater than twice above average	5
2452	19	Between above average and twice above average	4
2478	15	Between above average and twice above average	4
2486	9	Between 5 and average	2
2494	39	Greater than twice above average	5
2501	37	Greater than twice above average	5
2519	12	Between 5 and average	2
2527	5	Between zero and 5	1
2543		Zero	1
2551		Zero	1
2569	6	Between 5 and average	2
2577	1	Between zero and 5	1
2585	5	Between zero and 5	1
2593	5	Between zero and 5	1
2650	2	Between zero and 5	1
2668	10	Between 5 and average	2
2676	52	Greater than twice above average	5
2684	8	Between 5 and average	2
2692	3	Between zero and 5	1
2717	2	Between zero and 5	1
2759	13	Average	3
2767	23	Between above average and twice above average	4
2775	8	Between 5 and average	2
2783	9	Between 5 and average	2
2808	5	Between zero and 5	1
2973	14	Between above average and twice above average	4
2999	1	Between zero and 5	1
3004	16	Between above average and twice above average	4
3012	27	Greater than twice above average	5
3020	4	Between zero and 5	1
3038	6	Between 5 and average	2
3046	46	Greater than twice above average	5
3054	9	Between 5 and average	2
3062	3	Between zero and 5	1
3070	24	Between above average and twice above average	4
3088	56	Greater than twice above average	5
3096	6	Between 5 and average	2
3103	6	Between 5 and average	2
3111	7	Between 5 and average	2
3129	24	Between above average and twice above average	4
3137	2	Between zero and 5	1
3145	3	Between zero and 5	1
3153	29	Greater than twice above average	5
3161	4	Between zero and 5	1
3179	9	Between 5 and average	2
3187	22	Between above average and twice above average	4
3195	13	Average	3
3228	16	Between above average and twice above average	4
3236	1	Between zero and 5	1
3244	4	Between zero and 5	1
3252	15	Between above average and twice above average	4
3260	7	Between 5 and average	2
3278	9	Between 5 and average	2
3294	34	Greater than twice above average	5
3301	17	Between above average and twice above average	4
3327	5	Between zero and 5	1
3351	23	Between above average and twice above average	4
3369	15	Between above average and twice above average	4
3377	2	Between zero and 5	1
3385	11	Between 5 and average	2
3393	12	Between 5 and average	2
3400	1	Between zero and 5	1
3418	20	Between above average and twice above average	4
3426	10	Between 5 and average	2
3434	4	Between zero and 5	1
3442	3	Between zero and 5	1
3450	2	Between zero and 5	1
3468	1	Between zero and 5	1
3476	17	Between above average and twice above average	4
3484	13	Average	3
3492	4	Between zero and 5	1
3509	10	Between 5 and average	2

If summaried the results would look like this

Score	Score Name	Count or UNIT ref
1	Between zero and 5	43
1	Zero	3
2	Between 5 and average	52
3	Average	5
4	Between average and twice above average	37
5	Greater than twice above average	17

Hope this helps

[xifeng_L · Accepted solution]

Hi @cottrera ,

You can refer to below table expression.

Score = 
VAR TempTable = ADDCOLUMNS(ALL('Table'[Unit Reference]),"Repairs",CALCULATE(COUNTROWS('Table')))
VAR AverageRepairs = ROUND(AVERAGEX(TempTable,[Repairs]),0)
VAR ScoreTable = 
    SELECTCOLUMNS(
        {
            ("Greater then twice above average",5,AverageRepairs*2+1,99999),
            ("Between above average and twice above average",4,AverageRepairs+1,AverageRepairs*2),
            ("Average",3,AverageRepairs,AverageRepairs),
            ("Between 5 and average",2,6,AverageRepairs-1),
            ("Between zero and 5",1,1,5),
            ("Zero",1,0,0)
        },
        "Score Name",[Value1],
        "Score",[Value2],
        "Min",[Value3],
        "Max",[Value4]
    )
RETURN
ADDCOLUMNS(
    TempTable,
    "Score Name",
        MAXX(FILTER(ScoreTable,[Min]<=[Repairs] && [Repairs]<=[Max]),[Score Name]),
    "Score",
        MAXX(FILTER(ScoreTable,[Min]<=[Repairs] && [Repairs]<=[Max]),[Score])
)

Demo - Distributed Averages.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[cottrera]

Amazing thank you

[cottrera]

Hi thank you for responding.  The actual dataset I am using  be provided due to company policy. However I have tried to provide some more information on what I would need the DAX to perform.

Note: I have amended score 1 & 2

Score Mame	Score	Min	Max
Greater than twice above average	5	> Sum repairs / count properties * 2
Between above average and twice above average	4	Sum repairs / count properties	Sum repairs / count properties * 2
Average Repairs	3	Sum repairs / count properties	Sum repairs / count properties
Between 5 and average	2	5	Sum repairs / count properties
Between zero and 5	1	0	5
Zero	1	0	0

[xifeng_L]

This information you provided is the criteria for scoring, now I want to know what expected result you need to return or I can't calculate it for you.

[xifeng_L]

Hi @cottrera , Can you provide your expected results?