8 Replies Latest reply on Apr 13, 2017 11:53 PM by seeta t

    Sorting stacked bar chart

    Peter Hanges

      Hi there,

       

      I'm running into a problem which should be an easy fix.  Using the Superstore database as an example, I want to created a stacked bar chart which looks like the picture attached that I had to manually reorder.  In addition to the descending order shown in the picture, I also need the colors to line up (I.e. Central should be the first color for all rows, then East, etc.). 

       

      I've attached a sample workbook which demonstrates the sorting problem I'm having, any help is appreciated!

        • 1. Re: Sorting stacked bar chart
          Michael Someck

          Hi Peter,

           

          Getting the colors in the right order should be simple enough. In the color card you can drag the regions into whatever order you would like.

           

          As for the sort by customer name: what exactly do you want to achieve with this viz? The reason your sort by Avg(Sales) isn't giving what you want is the following: you are currently sorting by average sales across all regions. What your chart is doing visually is that it's taking the average in each region and stacking the bars on top of each other.

           

          So, for example, Mitch Willingham does actually have the highest Avg sales at $1,751 (you can see this if you remove Region from color). However, when you add Region to color, you're summing Avg(Sales) from West ($1,920), South ($1,667), and East/Central (0). Hunter Lopez, on the other hand, had huge average sales for East ($5,261), which is why his bar is longer than the bar for Mitch.

           

          It's possible to come up with a formula that will sort based "stacked average by region". Is that actually what you're looking to do, though?

           

          Michael

          • 2. Re: Sorting stacked bar chart
            Peter Hanges

            Hi Michael,

             

            Figured out the colors, thanks!

             

            What I want to do is have Tableau sort by the TOTAL average amount of sales.  $10k in sales > 9k > 8k> etc. etc., regardless of the distribution of color (region, in this case). 

            • 3. Re: Sorting stacked bar chart
              Michael Someck

              Hey Peter,

               

              If I understand you correctly, in that case, the sort you have in your workbook is the correct sort. The reason it doesn't look like that is (as I mentioned above) the length of the bars in your viz are actually the sum of the averages of each region.

               

              This means that one person with great average sales in 1 region might have a longer bar that a person who actually has better total average sales. The sort though is actually correct (which is what you see if you remove Region from the color card). Does that make sense?

               

              Michael

              • 4. Re: Sorting stacked bar chart
                Peter Hanges

                Yea I get what you're saying -- Thanks! Took me a minute to understand what was going on here

                • 5. Re: Sorting stacked bar chart
                  Michael Someck

                  Can you help clarify what exactly you're looking for? My understanding is you are looking for:

                   

                  1) the length of each bar corresponds to the total average sales (i.e., the average sales across all regions)

                  2) the bar is broken down into 4 colors (regions), where the length of each color corresponds to each region's contribution to the total average.

                   

                  In other words, if 40% Mitch's sales take place in the West region, then 40% of his bar would colored with the West color. This would look something like the following:

                   

                  Screen Shot 2017-04-13 at 3.24.26 PM.png

                   

                  Is this what you're looking for? If so, you'll want to use a LOD calc that fixes the denominator (in the average calculation) to be the total number of sales (not just in the region). What I used is this:

                   

                  SUM([Sales])/SUM({FIXED [Customer Name]: SUM([Number of Records])})

                   

                  I dragged this to columns and Region to color. Does that get you what you need?

                   

                  Let me know!

                   

                  Michael

                  1 of 1 people found this helpful
                  • 6. Re: Sorting stacked bar chart
                    Michael Someck

                    Oh, whoops! You edited your reply as I was responding. I'll leave the above in case it's helpful

                    • 7. Re: Sorting stacked bar chart
                      Peter Hanges

                      Hey! Yeah I finally grasped what you were saying in your first comments, but I really appreciate your follow-up! I think I'm actually going to use the formula you just suggested, love the way that it's laid out.  Thanks again!

                      • 8. Re: Sorting stacked bar chart
                        seeta t

                        HI Peter,

                         

                         

                        Here is the solution

                        i.e first displayed  central followed by east,south,west

                        Hope it helps you

                        seeta!!!!