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