Forum Discussion
Matrix background color by specific values in Rows and Columns
Hi team,
I hope you are doing well!
I need to create a matrix with specific background color by rows and columns. For the moment I could specified color only for one condition Rows or Columns. But, I need to specify the color taking in account the values in rows and the values in the columns.
Eg. the below picture represents what I need to :
Image1. My requeriment.
I need to use the specific value in the columns and the specific values in the row for the background-color. Because the background color has a target by "STUDY & Days". The table above.
I create a measure:
This measure only I can specific the specific values in the column or specific values in the rows. But, my target colors (above image1) request me more accuracy.
Image3. The matrix Image4. Fields selected for the matrix (image3, left)
Did you know how I can create the background color for that matrix?
Perhaps, Are there alternatives for create my requeriment that you want to share with me?
I've tried use:
Switch with AND, Switch with &&
IF with AND, IF with &&
It for use the both conditions, specific values in rows and specific values in columns but I couldn't.
Note: I'm sorry if I have grammatics errors. I'm improving my english.
Thank you so much for your help.
.pbi attached:
(Click here for download Questions.pbi)
Regards,
Hernán
Painful but works
in Power Query
Click on Study --> Pivot Columns with %RT as Value= Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Study]), "Study", "%RT", List.Sum)
Relplace All nulls with 0
click on Days ect.. and Unpivot other columns
= Table.UnpivotOtherColumns(#"Replaced Value2", {"Days"}, "Attribute", "Value")Rename Attribute --> Study
Rename Value --> %RT
Change text color:
Text Color =
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 7, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 9, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 8, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 12, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 12, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 15, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 13, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 17, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) > 12, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) > 15, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) > 13, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) >17, "#FF0000"
)
)))))))))))
Background Color =
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 7, "#00FF00",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 9, "#00FF00",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 8, "#00FF00",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 12, "#00FF00",
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 12, "#FFFF00",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 15, "#FFFF00",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 13, "#FFFF00",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 17, "#FFFF00",
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) > 12, "#FF0000",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) > 15, "#FF0000",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) > 13, "#FF0000",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) >17, "#FF0000"
)
)))))))))))
2 Replies
- kitgo2
Advocate I
Painful but works
in Power Query
Click on Study --> Pivot Columns with %RT as Value= Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Study]), "Study", "%RT", List.Sum)
Relplace All nulls with 0
click on Days ect.. and Unpivot other columns
= Table.UnpivotOtherColumns(#"Replaced Value2", {"Days"}, "Attribute", "Value")Rename Attribute --> Study
Rename Value --> %RT
Change text color:
Text Color =
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 7, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 9, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 8, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 12, "#00FF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 12, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 15, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 13, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 17, "#FFFF00",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "A" && MAX(Data1[Days]) > 12, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "B" && MAX(Data1[Days]) > 15, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "C" && MAX(Data1[Days]) > 13, "#FF0000",
if( Max(Data1[%RT]) = 0 && Max(Data1[Study]) = "D" && MAX(Data1[Days]) >17, "#FF0000"
)
)))))))))))
Background Color =
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 7, "#00FF00",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 9, "#00FF00",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 8, "#00FF00",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 12, "#00FF00",
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) <= 12, "#FFFF00",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) <= 15, "#FFFF00",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) <= 13, "#FFFF00",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) <= 17, "#FFFF00",
if( Max(Data1[Study]) = "A" && MAX(Data1[Days]) > 12, "#FF0000",
if( Max(Data1[Study]) = "B" && MAX(Data1[Days]) > 15, "#FF0000",
if( Max(Data1[Study]) = "C" && MAX(Data1[Days]) > 13, "#FF0000",
if( Max(Data1[Study]) = "D" && MAX(Data1[Days]) >17, "#FF0000"
)
)))))))))))
