Forum Discussion

Elisa112's avatar
Elisa112
Helper V
1 year ago
Solved

Comparing Matrix Values using Icons

Hi All

Im trying to compare values a matrix for student scores across assessment stages, I have created a summarised table and ranked scores which provides this:

 

Assessment Table:

 

I then create a matrix of the scores and produce this visual:

 

I need to show icons where the scores has either increased (up arrow), decreased (down arrow), or stayed the same (sideway arrow).  However the conditional formating does not allow me to compare individual scores within the matrix. for example for thefirst row since 5 is more than 3 I need to show an upwards arrow and then a downward arror for the next score which is 3.

I have blanked out the customer IDs for data protection protection purposes.

Any suggestions greatly appreciated.

 

 

Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    Hi Elisa112 ,
    Thanks for reaching out to Microsoft Fabric Community Forum.
    Based on the above scenario ,

    First fetch Previous Score using below DAX query :

    PreviousScore = 
VAR CurrentStudent = Sheet1[StudentID]
VAR CurrentStage = 'Sheet1'[AssessmentStage]
RETURN
    CALCULATE(
        MAX('Sheet1'[Score]),
        FILTER(
            'Sheet1',
            'Sheet1'[StudentID] = CurrentStudent &&
            'Sheet1'[AssessmentStage]= CurrentStage - 1
        )
    )

     

    create a new calucalted cloumn for change direction as below :

    ScoreTrend = 
VAR Curr = 'Sheet1'[Score]
VAR Prev = 'Sheet1'[PreviousScore]
RETURN
    IF(ISBLANK(Prev), BLANK(),
        SWITCH(TRUE(),
            Curr > Prev, "UP",
            Curr < Prev, "DOWN",
            Curr = Prev, "SAME"
        )
    )

     create a matrix visual and apply conditional formatting  the icon set to display up, down, or same arrows based on the ScoreChange.

    Below is the expected output :

     

    If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

    If you continue to face issues, feel free to reach out to us for further assistance!



7 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    Hello Elisa112 

     

    Try this measure

     

    Score with Trend Arrow =

    VAR CurrentStage = SELECTEDVALUE('AssessmentTable'[Rank])

    VAR CurrentScore = SELECTEDVALUE('AssessmentTable'[Score])

     

    VAR PreviousScore =

        CALCULATE(

            MAX('AssessmentTable'[Score]),

            FILTER(

                'AssessmentTable',

                'AssessmentTable'[Student ID] = SELECTEDVALUE('AssessmentTable'[Student ID]) &&

                'AssessmentTable'[Rank] = CurrentStage - 1

            )

        )

     

    VAR TrendArrow =

        SWITCH(

            TRUE(),

            ISBLANK(PreviousScore), "",                      -- No arrow for first column

            CurrentScore > PreviousScore, UNICHAR(9650),     -- ↑ Up arrow

            CurrentScore < PreviousScore, UNICHAR(9660),     -- ↓ Down arrow

            CurrentScore = PreviousScore, UNICHAR(8594)      -- → Right arrow

        )

     

    RETURN

        FORMAT(CurrentScore, "0") & " " & TrendArrow

    Thanks,
     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated

    • Elisa112's avatar
      Elisa112
      Helper V
      1 year ago

      Hi pankajnamekar25 

      thank you so much for this, I have tried to use your solution in the matrix, but I am still getting the same result :

       

       

      Am I doing something wrong here?

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        1 year ago

        Hi Elisa112 ,
        Thanks for reaching out to Microsoft Fabric Community Forum.
        Based on the above scenario ,

        First fetch Previous Score using below DAX query :

        PreviousScore = 
VAR CurrentStudent = Sheet1[StudentID]
VAR CurrentStage = 'Sheet1'[AssessmentStage]
RETURN
    CALCULATE(
        MAX('Sheet1'[Score]),
        FILTER(
            'Sheet1',
            'Sheet1'[StudentID] = CurrentStudent &&
            'Sheet1'[AssessmentStage]= CurrentStage - 1
        )
    )

         

        create a new calucalted cloumn for change direction as below :

        ScoreTrend = 
VAR Curr = 'Sheet1'[Score]
VAR Prev = 'Sheet1'[PreviousScore]
RETURN
    IF(ISBLANK(Prev), BLANK(),
        SWITCH(TRUE(),
            Curr > Prev, "UP",
            Curr < Prev, "DOWN",
            Curr = Prev, "SAME"
        )
    )

         create a matrix visual and apply conditional formatting  the icon set to display up, down, or same arrows based on the ScoreChange.

        Below is the expected output :

         

        If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

        If you continue to face issues, feel free to reach out to us for further assistance!