Pivot Table/Chart to filter categories

[Maxthelion]

Many thanks in advance for any help on this one!

I have a list of customer satisfaction scores that are pulled in from a sharepoint list. The list is then used to create a pivot chart that is used in a web part on a dashboard in sharepoint. The chart is supposed to show average customer satisfaction scores per category per month.

The problem I have is that I can't filter the categories using a slicer, I can only filter the categories' values (i.e. the scores). I can filter by date though.

When I try to filter the various score categories/series (Support, Change Management etc) I only get the option to filter their values i.e. 1,2,3,4,5.

I want to be able to use a slicer or similar to be able to display either all of the series or just selected ones.pivottable.JPGpivotchart.JPG

[Pauleyb]

I haven't found a way to slice columns of data - maybe it exists, but I don't think that is the way pivot tables work. To achieve your goal, you will need to reformat your data source table with a column describing the type (e.g. Account, Change, etc.) then use that in your pivot chart to slice off of. I have updated some of your data to show this concept in the attached file (I only did the Jan and Feb dates).

[Maxthelion]

Thanks, I thought that was probably the key, but I still don't know how to get my data into that format automatically using formulas. Manual manipulation is out of the question as this is for a dashboard that needs to be left to run on Sharepoint without intervention.

In essence, I need to be able to turn the first table below into the second in order to be able to pivot it. Can anyone help?

Customer Date CSAT Category 1 Score CSAT Category 2 Score CSAT Category 3 Score
Cust1 01/01/14 1 4 2
Cust2 05/01/14 3 1 5
Cust3 10/02/14 4 3 2


Customer Date CSAT Category Score
Cust1 01/01/14 Category 1 1
Cust1 01/01/14 Category 2 4
Cust1 01/01/14 Category 3 2
Cust2 05/01/14 Category 1 3
Cust2 05/01/14 Category 2 1
Cust2 05/01/14 Category 3 5
Cust3 10/02/14 Category 1 4
Cust3 10/02/14 Category 2 3
Cust3 10/02/14 Category 3 2

[Andy Pope]

You can use formula to build table in new layout. See new table on original data sheet

The create chart and slicers off of that, see sheet3

[Maxthelion]

Thanks Andy, you're a star! I'm still trying to work out how you did it, but it clearly works.
Will it continue to work as the tables grow?

Sincere thanks for your input, I had been trying to solve that problem for a week.

[Andy Pope]

The new table will not automatically grow but the formula will work simply by copying the rows down.

First 2 columns of table calculate row and column position of data within source table. These are then used in INDEX formula by the remaining columns in the table.

[Maxthelion]

Hi Andy or anyone else,
Can you suggest a way of getting the data transferred between the tables automatically? For the dashboard to work I need this to not require manual intervention.

Many thanks,
Max

[Andy Pope]

So how does the source table update?

[Maxthelion]

It gets its data from a data connection to a sharepoint list.

[Andy Pope]

The only non manually ways are

1. Extend the second table in preparation for more data. You will need to exclude #REF entries from pivot table.

2. Use code to extend table. Perhaps run macro on workbook open

[Maxthelion]

Hi,
Can you tell me how to exclude #Ref entries from the pivot table please? I can use the filter on the pivot table but even when they're filtered out, the #REF! entries prevent me from being able to group by month.
Many thanks for any help.

[Andy Pope]

Add an extra field to test record for #REF and use that field as a Page Filter.

[Maxthelion]

Hi Andy or anyone else who can help.
The capability to filter out the lines containing #REF! has removed the capability to group the records by month. Is there anything that can be done to fix this please? I've been trying to find a solution myself but I'm getting nowhere.
Many thanks.

[Andy Pope]

Use a dynamic named range for the pivot table data source rather than the table.

PTDATA: =Table3[[#Headers],[Row]]:INDEX(Table3[BadData],'owssvr(1)'!$V$2)

where V2 contains the formula to locate first TRUE bad data record.

=MATCH(TRUE,Table3[BadData],0)-1

[Maxthelion]

Crikey, that's some clever stuff! You sir, are a genius.
Thank you so much for your help.