Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |