CODEWITHSUNDEEP
excelpivot-tableexcel-2013vba
ZestStat
ZestStat

Reputation: 51

PivotTable - Filter Row

I am working on creating a pivot table for the data I have. The data looks something like below: 

    ID  Policy_July Policy_June Policy_May  Policy_April            Label

    13  High        Med         High        High        High        Good
    2   Low         Low         Low         Low         Low         Good
    3   Low         Low         Low         Low         Low         Good
    4   High        Med         High        High        High        Good
    5   High        Low         High        High        High        Good
    6   Low         Low         Low         Low         Low         Bad
    7   High        Med         High        High        High        Bad
    8   High        Low         High        High        High        Bad

I am comparing results from July with other months. Is there a way I can add a filter for row to enable me to change "Policy_June" to "Policy_March"? Basically, instead of having a filter for column, can I have filter for row in pivot table? If so, can you please guide me on how can I do it?

Again, what I am looking for is to have a filter button above the pivot table that would enable me to change "Policy_June" to "Policy_April".

Secondly, is there a way I can have "Grand Total" as a column (like I have at the bottom of pivot table)? I am trying to sum the value of "Bad" and "Good" for "High" and show the result in the column in yellow.

Let me know if you need any additional clarification.

Upvotes: 0

Views: 1022

Answers (1)

MikeD
MikeD

Reputation: 8941

You can normalize your data by giving it a layout similar to this:

ID   PolicyMonth  Status  Label
13   July         High    Good
13   June         Med     Good
13   May          High    Good
...

and create a Pivot with ID in vertical (Row Labels) and Status in horizontal (Column Labels) direction. You then can filter Row and Column Labels for a single (or multiple) values.

Not sure though how you would like to process the crosspoint data (Status, Label) as you only can sum/count/average/min/max by default ... this possibly may require conversion of Low/Med/High into numbers (0,1,2) and an average for the row total.

Edit

Further to your comments I understand you want to analyze a Delta ...

open image in new tab to get larger size

  • columns A..G contain original data

  • columns H..L contain a numeric representation according to formula

    H5: =IF(B5="High",2,IF(B5="Med",1,0))

  • cells C1 and C2 contain the month names to be compared ... they are identical to headers in H4..L4; change as you like ... data validation using a list of values and in-cell drop-down is thinkable

  • column M - big trick - is calculated using INDEX and MATCH functions, i.e.

    M4: =INDEX(H5:L5,1,MATCH($C$1,$H$4:$L$4,0))-INDEX(H5:L5,1,MATCH($C$2,$H$4:$L$4,0))

  • Pivot table is created with Diff as Row Label and count(ID) as data, displaying the ones that stayed flat (0), jumped up or down by 1 or 2 levels ... this of course can be filtered to supress or highlight the ones staying equal.

  • Result in Diff could be further processed into a DiffText using nested IF's and displayed in pivot rows instead of Diff

  • in screenshot: 3 ID's stayed unchanged, 3 ID's improved by 1 level, 2 ID's improved by 2 levels

  • Pivot table can drill down to the ID level to show WHO were the good/bad guys if you add ID to the row labels

Hope this comes closer than my 1st attempt.

open image in new tab to get larger size

Upvotes: 2

Related Questions