Forum Discussion

Hernán's avatar
Hernán
Frequent Visitor
4 years ago
Solved

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. ...
  • kitgo2's avatar
    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"
    )
    )))))))))))