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

# Stacked bar chart with values not cumulated

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

Can you share and example of your data?

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

Hi Ken,

Is this useful?:

Thanks,

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

Ken,

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

Thanks,

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

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

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,

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

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

You're a legend Ken, thank you!

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

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