Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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
Thank You Amitchandak.
I Have created an example using Northwind data;
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
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
User | Count |
---|---|
81 | |
77 | |
64 | |
48 | |
45 |
User | Count |
---|---|
103 | |
44 | |
39 | |
39 | |
37 |