3 Replies Latest reply on Jan 8, 2019 4:36 AM by Sarah Butler

# Unsure if I need to change data in source?

Hi all,

I am very new to Tableau, only started using it in the last month or so and I am the first to use it in my organisation. Every year we do a survey asking people to type into a box what percentage of their annual revenue goes to spending on x. We usually just take the average % but this year my employer would like me to create a bar chart showing the different revenue bands of the companies surveyed on the x axis and the average percentage spend on said item on the y axis.

The problem I am having is the survey output we have simply gives us the number people typed in. Tableau only seems to be able to tell me how many people said 5(%) or whatever and I can average how many people answered the question, but I want an average of the actual percentage they gave (so say, people in this size company on average spend 5% of their revenue on x).

As for the bar chart, I have no idea how to move things around to get revenues along the x axis and percentage spend on the y axis, since I only have a column of numbered answers that tableau doesn't recognise as a % of anything. The furthest I've managed to get is the answers along the x and how many people gave that answer, but that's not very helpful to visualise.

I'm happy to change the excel sheet if I need to to get this to work, if anyone can help! Screenshot of not very helpful graph below.

• ###### 1. Re: Unsure if I need to change data in source?

To put the revenue bands on the x-axis, drag a discrete dimension field with the different revenue band assignments to the "Columns" shelf. To get the average % reported as a vertical bar, drag the [Answers] measure (convert your [Answers] field to a continuous measure using right-click > convert to measure and right-click > continuous if it isn't one already so that you can aggregate it) onto the "Rows" shelf. Right-click on the new pill in the rows shelf and select Measure (Sum) > Average to change the aggregation from a sum to an average.

• ###### 2. Re: Unsure if I need to change data in source?

I have been able to get the revenue bands along the bottom, but the problem I'm having is that the data has come out into excel as a distinct number from each person, as if they were text responses and they have been added to the answers pivot just like the text answers. The question was 'what percentage of your revenue do you spend on x annually? Type the number into the box' and the spit-out is just a string in excel of (for example) 2, 5, 1.66, 3, 9, 15, etc. I have pivoted my data in the source pane so I have made 'questions' and 'answers' columns, and I am filtering the questions in the sheet, then filtering the answers to exclude null. So the answers pill has come out as a dimension, not a measure, so I can only see how many people typed the above numbers and the averages given are the averages of the number of people who answered the question, not the average of the percentage itself.

When I tried the above I can't change the answers pill to a sum, only a count. It is only counting the number of people that typed in each of the answer, but I want to measure the answers themselves somehow. I've set it to continuous but I only have min/max/count/count (distinct) as options when I go down to Measure.

• ###### 3. Re: Unsure if I need to change data in source?

I think I figured it out! Copied the answers dimension and converted the copy to a measure. Changed the data type from a string to Number (decimal), then used that in the rows and converted to an average.