Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Matrix Calculation Help

Hello,

Can anyone please suggest how I can get the attached format report. I have RAG, Grade and count of employees columns. The Grades are A1, A2, A3, B1, B2 and B3 etc but they need to be split into two tables (A1 to A3 in first table and B1 to B3 in second table) and I need to do display sum of A1 to A3 in first table at the end and sum of B1 to B3 in second table. The only thing I am trying to find is how I can sum A1 to A3 and display in the table and also do the colouring for red,amber and green in the table. I am trying in matrix table but please suggest if there is better/easy way to acheive this.

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    Create a calculated column

    category_grade = LEFT([grade],1)

     

    Create a measure to use for coloring

    Measure = SWITCH(MAX([rag]),"Red",1,"Amber",2,"Green",3)

     

    Best Regards

    Maggie

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    Create a calculated column to show "A" for A1, A2, A3 or "B" B1,B2,B3, then add [category_grade] in the "Columns" field.

    category_grade = LEFT([grade],1)

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie (v-juanli-msft ),

       

      Thanks for your reply. You mentioned that I have to create calculated column, so can you please tell the logic for that column.

       

      Kind Regards,

      Nivas

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-juanli-msft ,

         

        Also I am not sure how you have given the colouring as I can only see colouring for summarization values in Power BI, cant see option based on text fields (in this case its Rag column).