How to customize sorting a bar chart by two columns

[Anonymous]

Hi!

I am working on a report where I am trying to show a stacked bar chart like below. The dataset consists of a series of units (by name) on the x axis and values on the Y axis. There are two issues here. The data have a Status flag telling if it is Finish (light green) or in Progress (dark green). I want the bar chart to be sorted first on Status and then on units alphabetically. The real case includes more stuff. But for simplicity, I want to keep this as small as possible. So one thing is coloring the bar chart. There could I may be use a conditional formatting rule or something. The worse is to create a sorting that is not supported by the visual. The sorting possibilies in the visuals are limited. I must either sort by Unit or value, but that is not what I want. Perhaps there are ways to customize the sorting. Here a sorting by two columns or a kind of measure rank could be a solution. I have googled and tried but I am stucked right now. Hope to get some help and perhaps a pbix example

Regards Geir

As a start point I have been looking at this pbix file (example)

https://1drv.ms/u/s!AnHMZqeRJMulqzp05nASn1f65fnE?e=u7ZJST

[Whigpresident]

Yeah, great.  However, this functionality exists in Excel, Reporting Services,  and pretty much every visualization tool on the earth.  You should not have to perform a line level calculation to dual sort.  Does not work in MDX for example.  Solution below is a workaround to a missing basic function.  

[BellC]

Seriously. A sorely missing functionality.

I'm familiar with making creative sorting solutions, for example calendar tables and hierachys using the sort by column in the Table view tab. But what if I want to use Column A as x-axis in one visual sorted by Column B then Column C, however in a different visual I want to again use Column A as x-axis but instead sort by Column D and then Column E. As a hack I can duplicate column A in my data model, create two additional columns with special logic combining columns B, C, D and E, then assign a different "Sort by Column" to each version of Column A in the data model, and finally build the visuals using different versions of the same column for the x-axis. Or an analogous, equally convoluted solutions using measures with excessive/confusing or hidden tool tips like @AlB suggested. 

@Microsoft - Please add sort by options to basic pbi visuals.  

[AlB · Accepted solution]

@geirf

Interesting question. Here's a first approach to the solution. In fact, I had the idea of how you were using the tooltips, as the chart can be sorted by them without necessarily having to show them. Create an additional measure that will determine the sort order dynamically:

Sorting Measure = 
IF([Total Harvested]>0, 0, 100) + SELECTEDVALUE(Unit[UnitSort])

This sets two blocks:

0 + Unit classification

100 + Unit Classification

The 100 can be changed, of course, as long as the two blocks can be differentiated correctly. You could also use decimals...

Then use that [Sort Measure] in the chart tooltip. You can now sort the chart by [Sort Measure] ascending and you will have the blocks in the desired order.

Now this will show [Sort Measure] in the tooltips. If that bothers you, as it is not user-relevant information (and could be confusing), you could hide the tooltip (all of them I'm afraid) and have the chart display the data labels so that all important information is displayed (I guess here to show [Total Harvested] and [Total Amount] in the tooltip is not an absolute requirement). You can also change the chart title so that it doesn't show all the tooltips. View everything at work in the attachment.

Let me know if you have any other ideas.

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

[Anonymous]

This is elegant and exellent and what I was looking for. Great kudos and I am very grateful. Saved my day :-). Just one thing how did you add the pbix file. I had to copy the link to my onedrive but you have done it otherwise. How do you managed that 🙂

[AlB]

---

---

@Anonymous 

Attaching files is one of the (few) privileges bestowed upon Superusers 😎

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Anonymous 

Can you share a (dummy) pbix that has the fields you want to sort by (status and units) and whatever else is necessary to reproduce the issue? So that we can run a few tests when trying to come up with a solution

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi and thanks for spending time on me.

Try to use this example file that you can download from onedrive. I have tried using a measure that tests for harvested numbers > 0 they should be colored "green" here and placed to the left and the units should be sorted by name inside the two groups (harvested (>0) and not harvested (=0)). The other should be colored red (has still in stock numbers)

https://1drv.ms/u/s!AnHMZqeRJMulqzp05nASn1f65fnE?e=u7ZJST