Help with DAX for Scaled Score Matrix

[NKumar]

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!

[wdx223_Daniel · Accepted solution]

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]

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.