Forum Discussion
How to customize sorting a bar chart by two columns
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)
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
