Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
NGC48
Helper I
Helper I

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.

1 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" ) )

14.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

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.

 

 

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:
 
example2.png
 
example1.png

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

14.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.