cancel
Showing results for
Did you mean:
Regular Visitor

## 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 Cooking 1 0 0.70 Driving 1 0.93 0 Reading 0 1 0.53 Singing 1 0 0 Sports 1 0.36 0

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 1 Man 1 Singing 90 Team 3 Man 1 Singing 35 Team 2 Man 4 Driving 30 Team 3 Man 2 Reading 80 Team 3 Man 4 Cooking 30 Team 1 Man 2 Reading 65 Team 1 Man 5 Cooking 85 Team 3 Man 5 Cooking 15 Team 1 Man 1 Sports 85 Team 1 Man 4 Sports 75 Team 1 Man 5 Driving 30 Team 2 Man 4 Sports 65 Team 2 Man 3 Sports 25 Team 2 Man 3 Reading 20 Team 1 Man 4 Sports 35 Team 1 Man 3 Singing 75 Team 2 Man 4 Singing 35 Team 3 Man 2 Reading 35 Team 3 Man 4 Driving 30 Team 3 Man 3 Sports 30 Team 2 Man 1 Driving 70 Team 1 Man 4 Driving 75 Team 1 Man 5 Cooking 85 Team 1 Man 5 Cooking 35 Team 3 Man 5 Cooking 50 Team 2 Man 3 Cooking 20 Team 3 Man 5 Cooking 55 Team 2 Man 2 Reading 75 Team 2 Man 3 Reading 65 Team 1 Man 3 Singing 75

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!

1 ACCEPTED SOLUTION
Super User
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 2
Super User
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)
Regular 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.