Forum Discussion
Solved
Distributed Averages
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
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~
6 Replies
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 & 2Score 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 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.
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 thisScore 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 helpsHi 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~
Amazing thank you
