Conditionally change colour of bar with maximum value - MonthDate/Measure

[JWST]

I am brand new to PowerBI, so this is probably a very simple question. I am practicing with some weather data and I have daily wind gust measures. I have a chart where I have created a mmm-YY date format (Month = FORMAT([Date],"mmm-YY") - which is not sorting correctly, but I'll try and figure that out) and I would like to simply change the colour of the one month with maximum wind gust measure. 

Please can anyone assist with how I might do that?

[v-karpurapud]

Hi @JWST 

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank You!

[v-karpurapud]

Hi @JWST 

It's been a while since we last heard from you regarding the query and were following up to check if you have found a resolution. If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank You!

[FreemanZ]

hi @JWST ,

not sure i fully get you. 

supposing you have a data table like:

Date	Gust
1/1/2024	3
1/16/2024	5
1/31/2024	7
2/15/2024	5
3/1/2024	2
3/16/2024	6
3/31/2024	1
4/15/2024	9
4/30/2024	6
5/15/2024	3
5/30/2024	5
6/14/2024	3
6/29/2024	7
7/14/2024	4
7/29/2024	10
8/13/2024	4
8/28/2024	1
9/12/2024	7
9/27/2024	6
10/12/2024	1
10/27/2024	2

try like:

1) add a calculated columne like:

Month = FORMAT([Date],"yy/mm")

this can solve the sorting issue.

2) plot a column chart and apply conditional formatting (choose Field Value for Format Style) for the column color with a measure like:

HighlightMax = 
VAR _max = MAXX(ALLSELECTED(data), [GustMeasure])
VAR _maxmonth = MAXX(FILTER(ALLSELECTED(data), data[Gust]=_max), data[Month])
VAR _result = IF(MAX(data[Month])=_maxmonth, "red")
RETURN _result

it worked like:

Please find the attachment for more details. 

For more about conditional formatting, check:

https://powerbidocs.com/2020/11/02/conditional-formatting-by-field-value-in-power-bi/

https://www.datacamp.com/tutorial/guide-to-power-bi-conditional-formatting

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

[v-karpurapud]

Hi @JWST 

Thank you @techies , @tharunkumarRTK  and @powerbiexpert22 for your quick responses. 

We were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.  Could you please confirm if your query has been resolved? If it has, kindly mark the helpful response and accept it as the solution. This will help other community members resolve similar issues more efficiently. If not, please provide detailed information so we can assist you better.

Thank you

[JWST]

I have tried all of the responses so far and none of them work, so I have not accepted a solution yet.

[v-karpurapud]

Hi @JWST 

Could you please provide the detailed information and the expected output format along with a sample of your data? This will help us analyze the issue more clearly and ensure we provide you with the most accurate and effective assistance to resolve it.

Thank You.

[powerbiexpert22]

Hi @JWST ,

please see below pbix 

https://drive.google.com/file/d/10DqjVbbOWsGcmvdN9-m5Za-5wcQPZbDD/view?usp=drive_link

[JWST]

When I apply this to my data with the same formula, all of them show as the same colour (I have chosen green highlight and grey standard colour). When I check in the table view, all of them are grey. 

[techies]

Hi @JWST For year- month sorting, create this column in the date table

YearMonth = YEAR('date'[Date]) * 100 + MONTH('date'[Date])

You already have this monthly column in the date table

MonthLabel = FORMAT('date'[Date], "mmm-yy")

Go to monthlabel column, sortby from the menu- yearmonth column

Have created the below 3 measures for reference

Monthly Gust =

CALCULATE(

    SUM(weather[Wind Gust]),

    REMOVEFILTERS('date'[Date])

)

Max Wind Gust Value =

CALCULATE(

    MAXX(VALUES('date'[MonthLabel]), [Monthly Gust]),

    ALL('date')

)

Bar Color =

VAR MaxValue = [Max Wind Gust Value]

VAR CurrentValue = [Monthly Gust]

RETURN

    IF(CurrentValue = MaxValue, "#FF0000", "#C0C0C0")  

Next, add a clustered column chart, monthlabel, monthlygust

For conditional formatting

Go to column, click on the fx button,

click on the fx button, format by --- field value

based on the field---- bar color (measure)

[JWST]

I cannot seem to get this to work. I don't have any filters on, so I have removed the REMOVEFILTERS part of the first column - I assume that's ok? I have:

Monthly Gust =

CALCULATE(SUM('Weather Data 2024'[WindkmhGust]))

Max Wind Gust Value =

CALCULATE(

    MAXX(VALUES('Weather Data 2024'[MonthLabel]), [Monthly Gust]),

    ALL('Weather Data 2024')

)

Monthly Gust has the error "Column Monthly Gust cannot be found or may not be used in this expression". This is despite the fact I can see it right there and it works fine on its own before I try and add anything else. Any idea why it's doing that?

[techies]

Hi @JWST Let's debug this

You can try this by creating a summarized table

MonthlyWindGusts =

SUMMARIZE(

    'weather',

    'weather'[MonthLabel],

    "MonthlyGust", SUM('weather'[Wind Gust])

)

Then add a new column to this summarized table

MonthlyWindGusts =

VAR MaxGust =

    MAX(MonthlyWindGusts[MonthlyGust])

RETURN

        IF([MonthlyGust] = MaxGust, "Highlight", "Normal")

Then go to columns> click on fx button>

Does it change?

[tharunkumarRTK]

Conditional formatting

1. Create a Conditional formatting measure 

CF =
Var __Maxvalue = MAXX(ALL(DateTable[Month]), [wind guest measure])
Var __Windguest = [wind guest measure]
Var __Result = if(__maxvalue = __Windguest, "Green", "Blue")
return __result

2. Open the format settings options of your bar chart and open bars section. Next to color you will find "Fx" option.

3. Open it and change the option to "Field value" from the list of measures select the new CF measure you created and thats it

Sorting your month column

1. Create a Month-YearSort column with the below formula 

MonthYearSort = Month([Date]) * 100 + Year([Date]) 

2. Select your actual Month column and then on the top menu bar you will find sort by drop down then in the dropdown select MonthYearSort column that you created in setp 1

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[JWST]

All rows return Green with this (DAX with actual field names below). When I take out the other lines to test, all the formula seems to do is to return the gust value for that day. To be clear, the data source is daily data (for other uses and mimicking more closely the actual data I use) but I am plotting it by month. (if you use R, it is from the climate package, ogimet_daily() source)

CF =

Var __MaxValue = MAXX(ALL('Weather Data 2024'[Month]),'Weather Data 2024'[WindkmhGust])

Var __WindGust = [WindkmhGust]

Var __Result = if(__MaxValue = __WindGust,"Green","Blue")

return __result