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

[MStaddon]

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.

 

[bobsan42]

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]

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]

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]

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]

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?

 

[MStaddon]

Finally cracked it. I knew Bobsan was on the right lines, so googled and re-framed my search a bit and hit upon this
https://dataonwheels.wordpress.com/...-10-charts-to-power-view-which-honor-filters/

So needed to add in a 'Count' Measure - then 'Rank' that using DAX and then add that Ranking to my Pivot table... To then filter to rankings of 5 or below.

Thanks all.