Inverting the grouping order in clustered/stacked charts

[Anonymous]

Let me preface this post by saying I've searched exhaustively and while I can find similar posts, I can't find anything that answers my specific need.

I'm have what is effectively a service desk report which I'm in the process of optimising to make it as adapatable as possible to customer needs. The essence of the call logging database that I'm workign with is two tables: one that contains call details and a calendar table being used for date lookups. The calls table has a relationship with the calendar table via a date key (basically a mask), which I then use to do things like group calls over specific timeframes. All calls logged on 01/01/2020 have that as a date key and the calendar table includes fields like day, month, year, day of week, week of year, etc.

I've probably overexplained that.

One of the fundamental views in any report like this is calls per month for the past x calendar months (3 months in my case). The simplest way to display this is as a clustered or stacked bar chart. I use a relative date filter, make the month the legend and the call totals the value - easy. I have two fields to use to display the month using my calendar table: YYYYMM and month name. YYYYMM is automatically sorted from smallest to largest (e.g 202005, 202006, 202007) and I have month number (5, 6, 7) as my sort by column for month name to make sure it doesn't get ordered alphabetically when I use it.

I hope this makes sense so far.

What I'm trying to achieve is in my chart I would like to be able to set (or simply invert) the order of the months in each grop. Instead of the most recent month being last/right, I would like it to be first/left. In my case it's not as simple as an extra column with a sort order - I can't think of a way to make it work with the YYYYMM field. If anyone else can, that would make my life a hell of a lot easier. I'm really hoping there's a simple way to do it though. 

Just in case some context helps, what I'm trying to achieve is a reportwide colour scheme inspired by the film Sin City. All normal data in various shades of grey with key metrics and values in red to draw the consumer's attention. This would include breached thresholds and SLAs, that sort of thing. In the case of clustered charts like I've described, I would like the most recent month to be in red, as it's the key piece of data. I realise that I can maunally set the colour for each month in the legend, but when I need to generate the report again next month, I'll have to do it all again because it'll try to force 202007 to be red still. I'm trying to avoid the wasted man hours by using a simple colour theme of colour #1 being red and the rest being different shades of grey. I've figured out how to make sure that all key information in the report is coloured red except for these clustered/stacked bar charts.

I really hope that all make sense. It does in my head, but that doesn't help here necessarily. If anyone can offer some advice, I'd appreciate it.

[fhill]

Sorry, maybe side stepping your question, or maybe you did something like this already and it didn't work.  Why can't you use Date Logic to flag just the last X months you need in a new column, then Conditional Format your data based on that new Column?

[amitchandak]

@Anonymous , Pretty long description. Not sure that I got it. In case the visual do not have a legend and conditional formatting is allowed.

You can have measure like

Month Type = Switch( True(),
Date([Date]) = eomonth(Today(),-1),"Blue" , //Last Month
Date([Date])= eomonth(Today(),0),"Green" , //This Month
"Red"
)

And use this with field option.

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Anonymous]

@amitchandak Thanks a lot for the response, Amit. Yes, I'm sure I overexplained it. Let me try it with images, the way I should have the firrst time around.

This is what one of my basic visuals looks like:

As you can see, the order of the grouping (and legend) is ascending - smallest to largest. Right now those colours are being manually set. In order to make sure that colour #1 in my theme (red) is used for the most recent month (202007 in the above image), I can either do it manually, as it is now, or I must make sure that the most recent month is the first month in the cluster, which is automatically coloured using colour #1 in the theme. In other words, the order of the months needs to go from July -> June -> May.

Does that help at all?

Thank you for the links. I'm going to go through them now and see if I can find a way to makethat work for me.

@fhill Thanks for the advice. If I tabulated the data, it would really make it a lot simpler, and I'm keeping that as a last resort. Unfortunately this report is targeted at upper management and EXCO, so I need to make it as simple and consumable as possible. I've seen in presentations to non-IT executives how their eyes started to glass over as soon as they seen a grid of data, so I'm trying very hard to avoid it.

[fhill]

Yes, multiple people have asked to be able to customize 'Legend' values, but it just doesn't seem easy or possible in Power BI at this time.

While not a perfect solution, one Option I've seen is to customize the Theme to 'Storm' and then you can manually choose the colors you want for the First 8 Legen Elements.  ** However, if you keep more than 8 Legend Values in data, it will start over again at Value 1 when it hits 9 values. **  😞 

There are several support posts about 'Customize Legends' with links to enhancment requests on the forums you shoudl reivew and vote to prioritize.  Sorry I couldn't help more...

[Anonymous · Accepted solution]

@fhillThanks a lot for all the info. That's exactly what I'm working with right now. I've created my own basic theme using the colour scheme that I want:

With red as colour #1, it means that it's walys the default colour and is therefore always, for example, the biggest value in a pie chart, which is what I want to draw the reader's attention to. Unfortunately this means that it's also always the first colour used in a clustered bar chart, so it's always applied to the oldest day/month/year in a relative date range rather than the most recent.

Yes, customising the legend would be ideal but since I'm not able to do it I'm instead chasing the re-ordering of values.

[amitchandak]

@Anonymous , My only doubt is we can not have conditional formatting on the legend. 

[Anonymous]

@amitchandak  Agreed. That would be first prize, but no such luck. I can do it manually if I have to, which is made a bit easier now thanks to format painter, but my ideal would be a report that I just open and it is immediately formatted correctly. I'm not committed to a clustered or stacked bar graph, it just seems like the logical way to do it.