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

       

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

      Hi Xiaoxin Sheng, how wuld this work if I applied a date hierarchy; for example week number down to weekday?

      if I ahve a day that matches the min I see that may work but not the max? for example the max for all Fridays could be 265, not single Friday will have the same value.

       

      Regards, Noel