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!
Solved! Go to Solution.
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.