Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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~
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
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~
Amazing thank you
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 |
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |