Forum Discussion
Matrix background color by specific values in Rows and Columns
- 4 years ago
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"
)
)))))))))))
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"
)
)))))))))))