PDA

View Full Version : Help with Excel Rankings Report using Pivot Table Data



kencasehall
2017-06-22, 06:48 PM
Hey everyone! Need help with this task...
My main question is: Is there a formula that can return the company with the highest total sum of multiple fields in a PivotTable? For example, somehow revising the GetPivotData function to show the company with the highest sum of revenues in Consumer Marketing + Business Marketing + Social Media Marketing (all field names within the pivot table).


Please feel free to read below for more details to help guide your question or recommend a different solution:


-The PivotTable published by a research company shows a list of hundreds of companies and their revenues in 2015 and 2016 in certain areas (functions, business units, whatever). You could think of it as having 500 companies, their 2015 revenue in that market, and their 2016 revenue in that same market. We currently view it as Company Name along Column B (the Y-axis) and each "Area" on the top the x-axis with the "Year" below (Internet 2015 | Internet 2016 | Social Media Marketing 2015 | Social Media Marketing 2016 | etc....)


-I am currently trying to show the top 20 rankings based on 2016 revenues in each area. Sometimes our definition perfectly aligns with the report (i.e. Internet = Internet) while other times it does not (i.e. Marketing = Consumer Marketing + Business Marketing + Social Media Marketing). This requires multiple areas to be considered when determining rank.


-What I am trying to create is a separate sheet that could be used in the future when the publishers create an updated version of the PivotTable. This would reduce our time required to rank everyone and then see where we rank.


-Here's an example of what it would look like. I can't share the report since it's confidential so I just made this (see below). My process and formulas are below, but this requires me to copy and paste each section separately and calculate.
Column B: These are just input numbers "1" "2" etc...
Columns C & D: I used =Index( ,Match() ) to find the corresponding company name to the data
Column E: I used =LARGE() to map to Column B and the data to find the corresponding value according to the rank in Column B.
Columns F-K: I used the GetPivotData function to find the corresponding revenues in certain markets.


6992

NBVC
2017-06-22, 07:12 PM
Perhaps you can add a calculated field to the Pivot table that sums those 4 categories for each company, then you can use GetPivotData on that new sum field....

kencasehall
2017-06-22, 10:50 PM
Perhaps you can add a calculated field to the Pivot table that sums those 4 categories for each company, then you can use GetPivotData on that new sum field....

I'm not trying to manipulate the Pivot Table at all. I have attached a file that I made as an example of what I'm looking for with comments to better explain it and would appreciate any help on this.

Thanks,
KH

Ed Kelly
2017-06-23, 01:11 PM
Ken

Here is a quick and dirty, not fully clear as to what you exactly want... reach out to me if you want this finessed/radically altered

Index slicer will remove company name clutter in that slicer, Marketing_CBS will consolidate the 3 areas of marketing. Just refresh RData tab contents with updated info. (Dynamic folder would be a better way to go and of course all of the potential iterations built into the query that you think you need)

Knock em dead Tiger!

kencasehall
2017-06-23, 03:57 PM
Ken

Here is a quick and dirty, not fully clear as to what you exactly want... reach out to me if you want this finessed/radically altered

Index slicer will remove company name clutter in that slicer, Marketing_CBS will consolidate the 3 areas of marketing. Just refresh RData tab contents with updated info. (Dynamic folder would be a better way to go and of course all of the potential iterations built into the query that you think you need)

Knock em dead Tiger!

Thanks Ed but that's not quite what I'm looking for. I've attached an updated version of my example that can hopefully clarify it. I'm looking for rows 40-44 to be filled in appropriately with a combination of formulas that I can't figure out. Obviously I can find the answers from looking at the PivotTable, but again, we are looking at a much larger file and trying to automate this process and not alter the PivotTable.

My assumption is that there is a formula for Column E that can show the highest total revenue based on the combination revenues from each category (i.e., Highest "Analytics" total revenue by finding the highest combination of revenue from Analytics and Big Data) and then using this value to populate the other cells.

Thanks everyone
6997