Calculating a measure using highest values from different categories

[MrBenn]

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

[Vvelarde]

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]

[MrBenn]

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

[MrBenn]

Hi - can anyone help with this?

Thanks in advance

[MrBenn]

Hi

Can anyone help?

[Vvelarde · Accepted 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.

[MrBenn]

Amazing - thank you very much!