Forum Discussion

NGC48's avatar
NGC48
Icon for Helper I rankHelper 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 probl...
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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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

      • NGC48's avatar
        NGC48
        Icon for Helper I rankHelper I
        6 years ago

        Thank You Xiaoxin Sheng 😊 is this the only way you would consider achiveing this? could the formtting be seperated from the calculation and still work as a sperate measure?

         

        Regards, Noel