Excel 2016 Data Model - Pivot Table Value Filter 'Top 5' not working as desired

MStaddon

New Member
Joined
Feb 1, 2019
Messages
6
Hi all,

Long time reader/lurker, but a first time poster here.

I have developed a dashboard within Excel 2016 to bring together Customer Feedback data from 3 different sources (at present - longer term will add more to the mix), and used Power Pivot to help with this as contrast and comparison between the different sources was key, as well as my (unwise?) desire to have a single set of 'organisational' slicers that can be set once, and the relevant filters applied to all of my various Pivot Tables and Pivot Charts in one go.

One (fairly simple, I thought) chart I have is a Column chart showing submissions of positive feedback (not captured elsewhere) to our central repository, intended for use to show the most consistent submitting departments we have.

I currently have the data in a simple Pivot table along the lines shown below - with a department names and then a count of the numbers of (monthly) submissions received, sorted into descending order of total submissions received


E.g


Dept Submissions


Area 1 45
Area 2 44
Area 3 44
Area 4 43
Area 5 42
Area 6 42
Area 7 42
Area 8 41
Area 9 28
Area 10 12


What I'd like to be able to do is have a graph showing the Top 5 Departments, and how many submissions they have made across the time-frame selected using one of my slicers - however when applying this to the data set listed above rather than it presenting me with the Top 5 (and tied for 5th place) Departments, it simply cuts off at the 5th Department in the list. Any ideas how I get around this?


So I would hope/expect Excel to return


Dept Submissions


Area 1 45
Area 2 44
Area 3 44
Area 4 43
Area 5 42
Area 6 42
Area 7 42


But instead I am only getting


Dept Submissions


Area 1 45
Area 2 44
Area 3 44
Area 4 43
Area 5 42


Having done other tests with other 'normal' pivot tables - a 'tie' condition for 5th place seems to create the situation that I would prefer, but for some reason the Power Pivot and Data Model system that I've opted to employ for my task has decided to go off and do it's own thing.

Hoping that makes sense - and hoping someone can help me out.


Thanks in advance.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,843
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Add a column with Rank.AQ or Rank.AVG (check whichbone suits you). Then include it in the pivot and use it to filter. Otherwise you get 5 because you asked for 5. :)
 

MStaddon

New Member
Joined
Feb 1, 2019
Messages
6
Hi bobsan,

Thank you for that, but I don't think that's going to solve my issues (and I've realised I've probably not explained myself very well actually).

My 'raw' data has monthly submissions of Positive Feedback reported in by the different departments and areas, and I have then pivoted the data to show the number of monthly submissions received in a selectable time-frame.

So the raw data might be

Dept Month No. of Positive Comments Received

Area 1 Jan-18 101
Area 2 Jan-18 57
Area 3 Jan-18 123
Area 4 Jan-18 42
Area 1 Feb-18 96
Area 2 Feb-18 66
Area 4 Feb-18 12
Area 5 Feb-18 52
Area 1 Mar-18 111
Area 2 Mar-18 23
Area 5 Mar-18 45


And while the Total (sum) of the data across Jan-Mar 2018 might read

Dept Total Positive Comments Received

Area 1 308
Area 2 146
Area 3 123
Area 4 42
Area 5 97

The Total (count) of the submissions received would be

Dept Total Submissions Made

Area 1 3
Area 2 3
Area 3 1
Area 4 2
Area 5 2


And if I wanted to see the 'Top 3' Submitting areas, I would expect/want Excel to bring up

Dept Total Submission Made

Area 1 3
Area 2 3
Area 4 2
Area 5 2


Would adding a Measure in my Data Model 'table' (using RankX??) give me any joy do you think?

Thanks again - hoping to crack this today if I can.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,843
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
I can't pretend to completely understand the idea, but AFAICS you need to include a count of the months with submissions and then apply Value Filter on this field.
 

MStaddon

New Member
Joined
Feb 1, 2019
Messages
6

ADVERTISEMENT

Possibly. Have just run a quick experiment/test by pulling my data out of my dashboard spreadsheet and copying and pasting it in to a brand new sheet.
It seems that Excel's Top 10 Value filter within a Pivot table behaves differently depending on the 'source' data of the pivot itself.

So if I build a pivot table in the conventional way (using the raw data cells as the source), the 'Top 10' filter will include any Areas who are in a tie for 10th place.

But if I instead build the pivot table using data pulled from the 'Data Model' - Top 10 means Top 10 (with any ties being settled by the order the of the data in the Model).

It's going to be something really simple to resolve, I'm sure ... but at the moment nothing I'm trying is working.
 

MStaddon

New Member
Joined
Feb 1, 2019
Messages
6
Thought I had cracked it just now. Found that I can 'Convert to Formulas' within my Pivot Table that's causing me the problems, and that the filters etc. still all work. However, when I then add a 'better' ranking formula beside the pivot table data - the sorting and filtering element of the Pivot table (predictably...) stops working properly. So I've got it going wrong in a different way now, at least.

Anyone else able to offer any more ideas at all?
 

Forum statistics

Threads
1,141,715
Messages
5,708,052
Members
421,541
Latest member
Akidev

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top