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 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

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

  • NGC48's avatar
    NGC48
    Helper 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
    Helper 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