Forum Discussion

Sow-1's avatar
Sow-1
New Member
1 year ago

conditional formatting

I have created a matrix table with planned and available columns. With the selection of slicer, matrix table shows the data in hours and %.

I need to put the conditional formatting for planned column in matrix table like-

if hours is >8 then red else no color.

if % is >100 then red else no color.

this is for daily data.

There is also a condition that if this matrix table is drill up to weekly then

if hours is >40 should be red else no color.

if % is >100 for a week then it should be in red.

There is also a condition that if this matrix table is drill up to monthly then

if hours is >160 should be red else no color.

if % is >100 for a week then it should be in red.

Need Help

4 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Sow-1 

    Measure:

    Drill Level = 
    SWITCH(
    TRUE(),
    ISINSCOPE('Date'[Date]), "Daily",
    ISINSCOPE('Date'[Week]), "Weekly",
    ISINSCOPE('Date'[Month]), "Monthly",
    BLANK()
    )
    Format Planned = 
    VAR CurrentLevel = [Drill Level]
    VAR Hours = SUM('Table'[Planned Hours])
    VAR Percent = SUM('Table'[Planned %])
    RETURN
    SWITCH(
    TRUE(),
    CurrentLevel = "Daily" && Hours > 8, 1,
    CurrentLevel = "Daily" && Percent > 100, 1,
    CurrentLevel = "Weekly" && Hours > 40, 1,
    CurrentLevel = "Weekly" && Percent > 100, 1,
    CurrentLevel = "Monthly" && Hours > 160, 1,
    CurrentLevel = "Monthly" && Percent > 100, 1,
    0
    )

    Use the Format Planned measure in Conditional Formatting for the Planned column in the matrix visual. Set formatting rules where 1 applies red color and 0 applies no color.

     

    💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

     

  • Ajithkumar_P_05's avatar
    Ajithkumar_P_05
    Helper I
    1 year ago

    Create this measure

    Condition_Daily_Data =
    SWITCH(TRUE(),
    SUM(ConditionalFormatting[Hours])>8,1,
    SUM(ConditionalFormatting[%])>100,1,0)
     
    Then apply conditional formatting using the rules in format style.

    Create a flag using your condition and apply a color.

  • anmolmalviya05's avatar
    anmolmalviya05
    Super User
    1 year ago

    please try below measure:

    Planned_ConditionalFormat =
    VAR CurrentLevel = SELECTEDVALUE('Date'[Hierarchy Level])
    VAR PlannedHours = SELECTEDVALUE('MatrixTable'[Planned_Hours])
    VAR PlannedPercent = SELECTEDVALUE('MatrixTable'[Planned_Percent])

    -- Define thresholds
    VAR ThresholdHours =
    SWITCH(
    CurrentLevel,
    "Day", 8,
    "Week", 40,
    "Month", 160
    )
    VAR IsRed_Hours = IF(PlannedHours > ThresholdHours, 1, 0)
    VAR IsRed_Percent = IF(PlannedPercent > 100, 1, 0)

    RETURN
    IF(IsRed_Hours || IsRed_Percent, 1, 0)