topic Re: Highlighting the max value in bar chart issue in DAX Commands and Tips

Highlighting the max value in bar chart issue

bassmaninaus_PB — Fri, 17 Jun 2022 01:05:55 GMT

Hi,

I'm trying to highlight the max value in my bar chart, I have it partially working however need help to finalise.

As you can see this works with selecting an individual date, however if I select a range of dates or all dates the highlighted bar reverts.

One date selected..

Multiple or all dates selected..

The DAX I'm using for the highlight is as follows, any help to get this working, appreciated!

Max Interval = VAR MaxInterval = MAXX(ALLSELECTED(Interval),[Avg Calls per Interval]) Return IF( [Avg Calls per Interval] = MaxInterval, 1, 0 )

Re: Highlighting the max value in bar chart issue

Whitewater100 — Fri, 17 Jun 2022 01:20:11 GMT

Hi:

You can create a slicer where you choose how long the period under scrutiny will be

New Table :

Period = GENERATESERIES(1, 180, 3)

It will look like this:

Then you just sub in your table and measure names instead of mine: Your fact table with a date field replaces my

Retailer_Sales[Ship_Date]. And you use your [Avg Calls per Interval] measure where I used [Last x Days Sales].

It's important to have date table, marked as date table and formed relationship with your interval table. The result can look like(see last image).

Max Last x Days =

var periodselected = Period[Period Value]

var maxsales =

MAXX(ALLSELECTED(Retailer_Sales[Ship_Date]),

[Last x Days Sales])

var maxdate =

CALCULATE(MAX(Retailer_Sales[Ship_Date]),

FILTER(ALLSELECTED(Retailer_Sales[Ship_Date]),

[Last x Days Sales] = maxsales))

var final =

CALCULATE(SUM(Retailer_Sales[Qty_Sold]),

FILTER(Retailer_Sales,

Retailer_Sales[Ship_Date] > maxdate - periodselected &&

Retailer_Sales[Ship_Date] <= maxdate))

return

final

I hope this helps! Below shows a mi value which just used min vs max in the same forumla. Good luck!

Re: Highlighting the max value in bar chart issue

bassmaninaus_PB — Fri, 17 Jun 2022 01:23:08 GMT

Great, thanks Whitewater100, will give it a go!

Re: Highlighting the max value in bar chart issue

Anonymous — Wed, 22 Jun 2022 07:23:11 GMT

Hi bassmaninaus_PB ,

Please create this measure and set it to the bar chart conditional format.

Max Interval = VAR MaxInterval = MAXX(ALLSELECTED(Interval),[Avg Calls per Interval]) Return IF( [Avg Calls per Interval] = MaxInterval, "Red" )

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Re: Highlighting the max value in bar chart issue

bassmaninaus_PB — Thu, 23 Jun 2022 00:20:40 GMT

Thanks Gao, I can't quite get it to work with the interval as the x axis. Selecting one date ie 6th June by itself works, not when both dates are selected in a slicer. Very small sample data attached.

Date	Calls	Interval
Mon 6 Jun 22	1.408978719	7:30:00 AM
Mon 6 Jun 22	1.867321194	7:45:00 AM
Mon 6 Jun 22	1.731516016	8:00:00 AM
Mon 6 Jun 22	1.850345546	8:15:00 AM
Mon 6 Jun 22	2.80098179	8:30:00 AM
Mon 6 Jun 22	3.41210509	8:45:00 AM
Mon 6 Jun 22	3.581861562	9:00:00 AM
Mon 6 Jun 22	4.75318122	9:15:00 AM
Mon 6 Jun 22	4.685278631	9:30:00 AM
Mon 6 Jun 22	7.163723124	9:45:00 AM
Mon 6 Jun 22	8.029481132	10:00:00 AM
Mon 6 Jun 22	9.608216323	10:15:00 AM
Tue 7 Jun 22	1.279234313	7:30:00 AM
Tue 7 Jun 22	1.695370777	7:45:00 AM
Tue 7 Jun 22	1.572071084	8:00:00 AM
Tue 7 Jun 22	1.679958315	8:15:00 AM
Tue 7 Jun 22	2.543056165	8:30:00 AM
Tue 7 Jun 22	3.097904783	8:45:00 AM
Tue 7 Jun 22	3.252029399	9:00:00 AM
Tue 7 Jun 22	4.31548925	9:15:00 AM
Tue 7 Jun 22	4.253839403	9:30:00 AM
Tue 7 Jun 22	6.504058798	9:45:00 AM
Tue 7 Jun 22	7.29009434	10:00:00 AM
Tue 7 Jun 22	8.723453269	10:15:00 AM

Any help appreciated.

Re: Highlighting the max value in bar chart issue

Anonymous — Thu, 30 Jun 2022 08:05:04 GMT

Hi bassmaninaus_PB ,

Please try this.

Max Interval = VAR _max = CALCULATE( MAXX ( SUMMARIZE('Table','Table'[Interval],"Avg",[Avg Calls per Interval]), [Avg] ), ALLSELECTED() ) VAR _colour = IF ( [Avg Calls per Interval] = _max , "red" ) RETURN _colour

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

How to get your questions answered quickly -- How to provide sample data

Re: Highlighting the max value in bar chart issue

bassmaninaus_PB — Thu, 30 Jun 2022 12:04:59 GMT

Magic, exactly what I wanted, thanks Gao!!