Matrix Calculation Help

[Anonymous]

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.

[v-juanli-msft]

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]

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]

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).

[v-juanli-msft · Accepted solution]

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