Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Can anyone help? From a table of scores like the one below, I need to calculate a total score using criteria from other columns and then providing the totals shown in bold below.
I need to find the top 2 scores from category 1, top 3 scores from category 2, and then the top 3 scores from any unused scores from the previous two categories and any category 3 scores, as shown below for a single person. Can anyone recommend a DAX calculation or calcualtions for this? Any help would be greatly appreciated - thank you!
Person | Season | Category | Score |
Name | Spring | 1 | 8 |
Name | Spring | 1 | 5 |
Name | Spring | 1 | 6 |
Name | Spring | 2 | 5 |
Name | Spring | 2 | 4 |
Name | Spring | 2 | 3 |
Name | Spring | 2 | 6 |
Name | Spring | 3 | 5 |
Name | Spring | 3 | 7 |
Scores for Name | Spring | 1 | 14 (best 2 from the 3 results) |
2 | 15 (best 3 from the 4 results) | ||
3 | 17 (best from the unused scores in cat1+2 and any cat3 scores | ||
Total for name | 46 | ||
Total score for all names | 10000 | ||
Average for all names | 50.0 |
Solved! Go to Solution.
Hi @MrBenn
sorry my delay to answer but have busy days at work.
one solution could be;
1. in Edit Query in Table Score add an Index Colum (start from 1)
2. Add another Column i named IndextoUseinTOP
IndextoUseinTOP= Scores[Score] + DIVIDE(Scores[Index],COUNTROWS(Scores))
3. Modify the measure:
SumTop2ScoreCategory1 = SUMX(TOPN(2,TOPN(2,FILTER(Scores,Scores[Category]=1),Scores[Score]),Scores[IndextoUseinTOP]),Scores[Score])
or you can try to just change
SumTop2ScoreCategory1ALT =
SUMX(TOPN(2,FILTER(Scores,Scores[Category]=1),Scores[IndextoUseinTOP]),Scores[Score])
Repeat this to measure to Category 2 and adapt to others.
hello @MrBenn
To resolve this a find a way using TopN, Union and Except
1. Create 2 measures to TOPs by Cat 1 y 2
SumTop2ScoreCategory1 = SUMX ( TOPN ( 2, FILTER ( Scores, Scores[Category] = 1 ), Scores[Score] ), Scores[Score] )
SumTop3ScoreCategory2 = SUMX ( TOPN ( 3, FILTER ( Scores, Scores[Category] = 2 ), Scores[Score] ), Scores[Score] )
2. Create a measure to TOP 3 to others scores
SumTop3ScoreOthers = SUMX ( TOPN ( 3, EXCEPT ( ALL ( Scores ), UNION ( TOPN ( 2, FILTER ( Scores; Scores[Category] = 1 ), Scores[Score] ), TOPN ( 3, FILTER ( Scores; Scores[Category] = 2 ), Scores[Score] ) ) ), Scores[Score] ), Scores[Score] )
3. Finally Sum the 3 measures
Total = [SumTop2ScoreCategory1] + [SumTop3ScoreCategory2] + [SumTop3ScoreOthers]
Hi Victor
Thank you very much for your help - although I think there is an issue with the DAX you provided - I don't think I gave enough detail.
The scores appearing under all of the measures calculate the tied values, so if a cat 2 set of scores has 3,3,3,3, the value returned is 12, but I am only interested in 3 scores, so the figure should be 9. Could you recommend a way to correct this?
Thanks
Ben
Hi - can anyone help with this?
Thanks in advance
Hi
Can anyone help?
Hi @MrBenn
sorry my delay to answer but have busy days at work.
one solution could be;
1. in Edit Query in Table Score add an Index Colum (start from 1)
2. Add another Column i named IndextoUseinTOP
IndextoUseinTOP= Scores[Score] + DIVIDE(Scores[Index],COUNTROWS(Scores))
3. Modify the measure:
SumTop2ScoreCategory1 = SUMX(TOPN(2,TOPN(2,FILTER(Scores,Scores[Category]=1),Scores[Score]),Scores[IndextoUseinTOP]),Scores[Score])
or you can try to just change
SumTop2ScoreCategory1ALT =
SUMX(TOPN(2,FILTER(Scores,Scores[Category]=1),Scores[IndextoUseinTOP]),Scores[Score])
Repeat this to measure to Category 2 and adapt to others.
Amazing - thank you very much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |