Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MrBenn
Helper II
Helper II

Calculating a measure using highest values from different categories

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!

 

    
PersonSeasonCategoryScore
NameSpring18
NameSpring15
NameSpring16
NameSpring25
NameSpring24
NameSpring23
NameSpring26
NameSpring35
NameSpring37
    
Scores for NameSpring114 (best 2 from the 3 results)
  215 (best 3 from the 4 results)
  317 (best from the unused scores in cat1+2 and any cat3 scores
  Total for name46
Total score for all names10000  
Average for all names50.0  
1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

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.

 

 

 




Lima - Peru

View solution in original post

6 REPLIES 6
Vvelarde
Community Champion
Community Champion

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]

 




Lima - Peru

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?

Vvelarde
Community Champion
Community Champion

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.

 

 

 




Lima - Peru

Amazing - thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.