Forum Discussion

NGC48's avatar
NGC48
Helper I
6 years ago
Solved

Clustered Column Conditional Formatting

Hi all,

I have what I feel should be a simle solution but cant work it out.

I have a column chart to display number of orders per weekday; I have calculated the Max & Min of the weekdays.

My problem is setting the column colours to match the result, i.e. if Thursday has the MIN value colour Red else Blue.

Tried the standard switch function wihtout luck 🙁.

Thanks in anticipation.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi NGC48,

    You can add a calculated column to your fact table to stored weekday and write a measure formula to use on condition color formation.

    Calculate column

    WeekdayName = FORMAT([Date],"ddd")

    Measure:

    color formatting =
VAR varTable =
    SUMMARIZE (
        ALLSELECTED ( 'fact' ),
        [WeekdayName],
        "Count", COUNTROWS ( 'fact' )
    )
VAR _min =
    MINX ( varTable, [Count] )
VAR _max =
    MAXX ( varTable, [Count] )
VAR _curr =
    CALCULATE ( COUNTROWS ( 'fact' ), VALUES ( 'fact'[WeekdayName] ) )
RETURN
    IF ( _curr = _min, "Red", IF ( _curr = _max, "Green", "Blue" ) )

    Regards,

    Xiaoxin Sheng

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try

     

     

    Color Category = SWITCH(true(),
Max(Sheet1[Weekday]) in weekday(Today()) && [value]=[Max weekly value],"Red",
"Blue"
)

     

    Can you share sample data and sample output.

     

     

    • NGC48's avatar
      NGC48
      Helper I
      6 years ago

      Thank You Amitchandak.

      I Have created an example using Northwind data;

      Max Daily Count =
      VAR ValueTable =
      UNION(
      ROW("Value", CALCULATE(COUNT(T_Orders[OrderID]),DateTable[WeekDayNum] = 1)),
      ROW("Value", CALCULATE(COUNT(T_Orders[OrderID]),DateTable[WeekDayNum] = 2)),
      ROW("Value",CALCULATE(COUNT(T_Orders[OrderID]),DateTable[WeekDayNum] = 3)),
      ROW("Value", CALCULATE(COUNT(T_Orders[OrderID]),DateTable[WeekDayNum] = 4)),
      ROW("Value", CALCULATE(COUNT(T_Orders[OrderID]),DateTable[WeekDayNum] = 5)),
      ROW("Value",CALCULATE(COUNT(T_Orders[OrderID]),DateTable[WeekDayNum] = 6)),
      ROW("Value",CALCULATE(COUNT(T_Orders[OrderID]),DateTable[WeekDayNum] = 7))
      )
      RETURN MAXX(ValueTable,[Value])
      I am using a basic datetable:
       
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi NGC48,

        You can add a calculated column to your fact table to stored weekday and write a measure formula to use on condition color formation.

        Calculate column

        WeekdayName = FORMAT([Date],"ddd")

        Measure:

        color formatting =
VAR varTable =
    SUMMARIZE (
        ALLSELECTED ( 'fact' ),
        [WeekdayName],
        "Count", COUNTROWS ( 'fact' )
    )
VAR _min =
    MINX ( varTable, [Count] )
VAR _max =
    MAXX ( varTable, [Count] )
VAR _curr =
    CALCULATE ( COUNTROWS ( 'fact' ), VALUES ( 'fact'[WeekdayName] ) )
RETURN
    IF ( _curr = _min, "Red", IF ( _curr = _max, "Green", "Blue" ) )

        Regards,

        Xiaoxin Sheng