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

We need to see your file.

https://answers.microsoft.com/en-us/msoffice/forum/all/why-a-sample-file-is-important-for-troubleshooting/9441ae3c-1e92-41c6-9a1f-5b377b08e5a5

Andreas.

Answer 3

Sorry, I've added one to the original post: March Survey Master.xlsx

Answer 4

March Survey Master.xlsx

Thank you.

Here is your modified sample file:
https://www.dropbox.com/scl/fi/59h9y1gxarfxvuek7vnio/81c030f2-3328-4c79-9001-ce92896f6ad3.xlsx?rlkey=o0vtc47g5ixx5txsy9drjuajc&dl=1

I agree with Ashish, we need to transform the data, because your data has headers at the top and headers on the left side of the data. This is what we called pivoted data. I loaded the data with Power Query select all columns with the answers, right-click and unpivot the columns, done.

I have gone one step further and created a related table from the unique answers with an Order column added, so the Pivot Table shows the answers in the correct order. For that all data is loaded into the Data Model and I've also created a relationship between the tables in there.

In the sheet I've added some slicers, so it's easier to filter the data.

And of course the Pivot chart is below.

Any questions?

Andreas.

Answer 5

Thanks Andreas, I wasn't able to work with this I kept getting the message that it wasn't supported in my version of Excel - perhaps as I'm on a Mac.