Clustered Column Conditional Formatting

[NGC48]

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.

[amitchandak]

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]

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:

[v-shex-msft · Accepted solution]

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]

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

[NGC48]

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