Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NKumar
Frequent 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     
Cooking100.70
Driving10.930
Reading010.53
Singing100
Sports10.360

 

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 1Man 1Singing90
Team 3Man 1Singing35
Team 2Man 4Driving30
Team 3Man 2Reading80
Team 3Man 4Cooking30
Team 1Man 2Reading65
Team 1Man 5Cooking85
Team 3Man 5Cooking15
Team 1Man 1Sports85
Team 1Man 4Sports75
Team 1Man 5Driving30
Team 2Man 4Sports65
Team 2Man 3Sports25
Team 2Man 3Reading20
Team 1Man 4Sports35
Team 1Man 3Singing75
Team 2Man 4Singing35
Team 3Man 2Reading35
Team 3Man 4Driving30
Team 3Man 3Sports30
Team 2Man 1Driving70
Team 1Man 4Driving75
Team 1Man 5Cooking85
Team 1Man 5Cooking35
Team 3Man 5Cooking50
Team 2Man 3Cooking20
Team 3Man 5Cooking55
Team 2Man 2Reading75
Team 2Man 3Reading65
Team 1Man 3Singing75

 

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
wdx223_Daniel
Super User
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)

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
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)

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors