Forum Discussion

NKumar's avatar
NKumar
Frequent Visitor
3 years ago
Solved

Help with DAX for Scaled Score Matrix

Hi all, I've been trying to create a DAX measure to generate scaled score (wihout intermediate tables) for a competition but unsuccessful.

 

This is how the end matrix is required in Power BI

Scaled Score
 Team 1     Team 2     Team 3     
Cooking100.70
Driving10.930
Reading010.53
Singing100
Sports10.360

 

The forumula that is used to calcuate the scaled score:

Scaled Score for an Activity = ((Sum of the respective Team's Activity Mins) - (Minimum of Activity Mins across teams)) /

((Maximum of Activity Mins across teams) - (Minimum of Activity Mins across teams))

For e.g., Scaled Score of Team 3 for Cooking = (150-20)/(205-20) = 0.703

 

This is the source table, named Activity: This table contains the list of activities (with duration in mins) perfomed by each candidate.

Team       Candidate Name     Activity     Activity Mins   
Team 1Man 1Singing90
Team 3Man 1Singing35
Team 2Man 4Driving30
Team 3Man 2Reading80
Team 3Man 4Cooking30
Team 1Man 2Reading65
Team 1Man 5Cooking85
Team 3Man 5Cooking15
Team 1Man 1Sports85
Team 1Man 4Sports75
Team 1Man 5Driving30
Team 2Man 4Sports65
Team 2Man 3Sports25
Team 2Man 3Reading20
Team 1Man 4Sports35
Team 1Man 3Singing75
Team 2Man 4Singing35
Team 3Man 2Reading35
Team 3Man 4Driving30
Team 3Man 3Sports30
Team 2Man 1Driving70
Team 1Man 4Driving75
Team 1Man 5Cooking85
Team 1Man 5Cooking35
Team 3Man 5Cooking50
Team 2Man 3Cooking20
Team 3Man 5Cooking55
Team 2Man 2Reading75
Team 2Man 3Reading65
Team 1Man 3Singing75

 

This is the measure I wrote after a lot of research, but it isn't working as intended

 

Scaled Score = 
VAR MinValue =
    CALCULATE(
        MINX(
            SUMMARIZE(Activity, Activity[Team]),
            CALCULATE(
                SUM(Activity[Activity Mins])
            )
        )
    )
VAR MaxValue =
    CALCULATE(
        MAXX(
            SUMMARIZE(Activity, Activity[Team]),
            CALCULATE(
                SUM(Activity[Activity Mins])
            )
        )
    )
VAR TeamTotal =
    CALCULATE(
        SUM(Activity[Activity Mins]),
        ALLEXCEPT(Activity, Activity[Activity])
    )
RETURN
    DIVIDE(TeamTotal - MinValue, MaxValue - MinValue, 0)

 

 

 Any help in getting the DAX measure is appreciated. Thanks in advance!

  • Scaled Score = VAR _min=MINX(ALL('SampleData'[Team]),CALCULATE(SUM([Activity Mins]))) VAR _max=MAXX(ALL('SampleData'[Team]),CALCULATE(SUM([Activity Mins]))) RETURN DIVIDE(SUM('SampleData'[Activity Mins])-_min,_max-_min)

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    Scaled Score = VAR _min=MINX(ALL('SampleData'[Team]),CALCULATE(SUM([Activity Mins]))) VAR _max=MAXX(ALL('SampleData'[Team]),CALCULATE(SUM([Activity Mins]))) RETURN DIVIDE(SUM('SampleData'[Activity Mins])-_min,_max-_min)
    • NKumar's avatar
      NKumar
      Frequent Visitor

      Thanks a ton wdx223_Daniel! This is exactly what I wanted. 

      I was not aware of the ALL function, I'll research about its applications. Thanks again for the help.