I have a stacked bar chart with 2 sales values. I made a calculated column that adds the 2 together and I want to sort by that total number. I attempted to sort the sales agent by the total volume column that I made, but it just throws an error.
I also tried changing my calculation to SUM(Val 1) + SUM(val 2) and then changing the sales agent to sort on that, but that also isn't coming out right.
This is what I am working with:
I just found this video from Guy in a Cube: https://www.youtube.com/watch?v=1bp_e-ik7aA
It shows how you can resolve this by adding another series to the tooltip of your visual.
You can than hit the ellipsis of your visual and click Sort By, then you select the series you just added.
Just create a measure Total Sales (in your case), you'll probably need it in your data model anyway.
I solved his for my viz by a "hack". I am using a horizontal stacked bar chart.
I had a separate measure of sum of all other values. I added this as the LAST item in the stacked chart (so it would be all the way on the right). I sorted the chart by this measure. I then went into Formatting settings and made that data value the same colour as the background. Voila, we now have the correcct sorting AND the sum of all visible values appears to the right of the stacked columns. Two birds, one hack 😉
Are the colors two measures stocked on top of the other or just one measure with legends?
Would you mind posting a dummy data?
I played around with the line and bar chart but didn't like the look with the stupid line. Further the labels clutter everything up. What I ended up doing was adding a measure with my total value divided by 10,000 (or any number that makes the value very small so it is not visible on the stacks. You can then add this item to the values and then sort by it.
Only down side is it still shows up in the legend. Haven't figured out how to get it out of there yet.
You can use clustered bar chart to achieve your requiremnet:
Use CONCATENATE function to combine both the volumes column and sort the data based on that.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.