modifier
1 Topic
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!Solved860Views0likes2Comments