How can I stop getting duplicate values in PivotTable/Chart?

Question

Hey,

I'm fairly new to PivotTables and am trying to make my way through it as self-guided as much as possible.

I have a survey I want to report on using a PivotTable and PivotChart (as I want to be able to manipulate the view using filters/splicers). I've managed to have the table show how I want with questions down the left and responses (strongly agree - strongly disagree) along the top and in the chart how I want - screenshot included and copy of the table below.

	Column Labels
Values	Agree	Disagree	Neutral	Strongly agree	Strongly disagree	Grand Total
Wellbeing	393	24	179	416	2	1014
Tools & Support	393	24	179	416	2	1014
Feedback	393	24	179	416	2	1014
3 Years	393	24	179	416	2	1014
Proud	393	24	179	416	2	1014

As you can see, all values are the same but in the raw data, they're not so for example, Proud should display 687 Strongly Agree, 267 Agree and 59 Neutral. All data seems to show for the Wellbeing question. I'm assuming that's because I'm using the Wellbeing question to produce the Strongly Agree to Strongly Disagree labels in the column section but I can't find a way to display these without doing that.

Link to file: March Survey Master.xlsx

Is there a way to have the values show correctly? Do I need to change how the table displays? If so, is there a way to get the chart to show how I want?

Answer 1

Hi,

Your data needs transformation. I have done so using Power Query - see the worksheet named Transformed data. From here, i have created the Pivot Table. You may download the PBI file from here.

Hope this helps.

Answer 2

Thanks, Ashish! This makes sense from my initial glance so I'll spend more time reviewing it so I can understand fully for next time

Answer 3

You are welcome.