8 Replies Latest reply on Jul 8, 2018 4:41 PM by Ken Flerlage

    Stacked bar chart with values not cumulated

    Farshad Rastgouy

      I am using data on the average UK salaries.

       

      Part of the data gives the salaries at a particular percentile. I want to display these values on a bar chart that displays the salaries of each percentile.

       

      I've tried doing this with stacked bars, but stacked bars cumulate the values of these percentile salaries. I don't want the salaries to stack in value, I just want them displayed on the same bar.

       

      How do I go about doing this?

        • 1. Re: Stacked bar chart with values not cumulated
          Ken Flerlage

          Can you share and example of your data?

          • 2. Re: Stacked bar chart with values not cumulated
            Farshad Rastgouy

            Hi Ken,

             

            Is this useful?:

            Tableau Public

             

            Thanks,

             

            Farshad

            • 3. Re: Stacked bar chart with values not cumulated
              Farshad Rastgouy

              Ken,

               

              The actual csv file too big upload on here, but the data for UK salaries can be downloaded from here:

              https://beta.ons.gov.uk/datasets/ashe-table-8-earnings/editions/time-series/versions/1#id-dimensions

               

              Thanks,

               

              Farshad

              • 4. Re: Stacked bar chart with values not cumulated
                Ken Flerlage

                I've looked at this workbook, but I'm still not clear on exactly what you're trying to accomplish.

                • 5. Re: Stacked bar chart with values not cumulated
                  Farshad Rastgouy

                  Hi Ken,

                   

                  So when you see the stacked bar chart, you notice the values of the 10th, 20th, 25th, etc percentile add up on the x axis (average earnings). What I want to do is have the value displayed on the x-axis to reflect the value of the percentile.

                   

                  For example for Aberdeen City I'd want the colour that represents the 10th percentile to go up to 16,333 and then I'd want the colour of the stacked bar chart for the 20th percentile to go up to 19,929 on the x-axis, and so on.

                   

                  I've tried to do this in two different ways but neither has worked.

                   

                  Method 1:

                  Created a measure that represents each percentile

                  For the calculation for the 10th percentile I wrote --> IF  [Statistics] = '10th percentile' THEN [Earnings] ELSE 0 END)

                  For all subsequent percentiles I subtracted the previous percentile from the "Earnings" like so --> IF  [Statistics] = '20th percentile' THEN [Earnings]-[10th percentile] ELSE 0 END

                   

                  For some reason though the values of the 20th percentile and onwards are not subtracted from the previous percentile.

                   

                  Method 2:

                  Created one massive measure that sums up what is going on in the first method.

                  IF  [Statistics] = '10th percentile' THEN [Earnings]

                  ELSEIF [Statistics] = '20th percentile' THEN [Earnings]-(IF  [Statistics] = '10th percentile' THEN [Earnings] END)

                  ELSEIF [Statistics] = '25th percentile' THEN [Earnings]-(IF [Statistics] = '20th percentile' THEN [Earnings]-(IF  [Statistics] = '10th percentile' THEN [Earnings] END)END )

                  ELSEIF [Statistics] = '30th percentile' THEN [Earnings]-(IF [Statistics] = '25th percentile' THEN [Earnings]-(IF [Statistics] = '20th percentile' THEN [Earnings]-(IF  [Statistics] = '10th percentile' THEN [Earnings] END)END )END )

                  ELSEIF [Statistics] = '40th percentile' THEN [Earnings]-(IF [Statistics] = '30th percentile' THEN [Earnings]-(IF [Statistics] = '25th percentile' THEN [Earnings]-(IF [Statistics] = '20th percentile' THEN [Earnings]-(IF  [Statistics] = '10th percentile' THEN [Earnings] END)END )END )END )

                  ELSEIF [Statistics] = '60th percentile' THEN [Earnings]-(IF [Statistics] = '40th percentile' THEN [Earnings]-(IF [Statistics] = '30th percentile' THEN [Earnings]-(IF [Statistics] = '25th percentile' THEN [Earnings]-(IF [Statistics] = '20th percentile' THEN [Earnings]-(IF  [Statistics] = '10th percentile' THEN [Earnings] END)END )END )END ) END)

                  ELSEIF [Statistics] = '70th percentile' THEN [Earnings]-(IF [Statistics] = '60th percentile' THEN [Earnings]-(IF [Statistics] = '40th percentile' THEN [Earnings]-(IF [Statistics] = '30th percentile' THEN [Earnings]-(IF [Statistics] = '25th percentile' THEN [Earnings]-(IF [Statistics] = '20th percentile' THEN [Earnings]-(IF  [Statistics] = '10th percentile' THEN [Earnings] END)END )END )END ) END)END )

                  ELSEIF [Statistics] = '80th percentile' THEN [Earnings]-(IF [Statistics] = '70th percentile' THEN [Earnings]-(IF [Statistics] = '60th percentile' THEN [Earnings]-(IF [Statistics] = '40th percentile' THEN [Earnings]-(IF [Statistics] = '30th percentile' THEN [Earnings]-(IF [Statistics] = '25th percentile' THEN [Earnings]-(IF [Statistics] = '20th percentile' THEN [Earnings]-(IF  [Statistics] = '10th percentile' THEN [Earnings] END)END )END )END ) END)END )END )

                  ELSEIF [Statistics] = '90th percentile' THEN [Earnings]-(IF [Statistics] = '80th percentile' THEN [Earnings]-(IF [Statistics] = '70th percentile' THEN [Earnings]-(IF [Statistics] = '60th percentile' THEN [Earnings]-(IF [Statistics] = '40th percentile' THEN [Earnings]-(IF [Statistics] = '30th percentile' THEN [Earnings]-(IF [Statistics] = '25th percentile' THEN [Earnings]-(IF [Statistics] = '20th percentile' THEN [Earnings]-(IF  [Statistics] = '10th percentile' THEN [Earnings] END)END )END )END ) END)END )END )END )

                  ELSE 0

                  END

                   

                  But for this, only the 10th percentile is displayed and none of the subsequent percentiles.

                   

                  Thanks,

                   

                  Farshad

                  • 6. Re: Stacked bar chart with values not cumulated
                    Ken Flerlage

                    OK. If I understand you (sorry it's taking so long ), then what you want is for the sections of the bar to overlap so that they all start a 0 and go to their actual value. If that's correct, then there's a simple solution. Go to Analysis, then Stack Marks then turn it Off.

                     

                    This will turn off the stacking effect and cause them all to start from zero. You should see something like this:

                     

                    Is that what you need?

                    1 of 1 people found this helpful
                    • 7. Re: Stacked bar chart with values not cumulated
                      Farshad Rastgouy

                      You're a legend Ken, thank you!

                      • 8. Re: Stacked bar chart with values not cumulated
                        Ken Flerlage

                        No problem Farshad. This little feature is a hidden gem!!