Hey there! First time post! I seem to always fall short with filtering the data in my pivot tables (Excel 2010), end up manually doing this which defeats the purpose, takes more time and is less accurate.
To set the scene, my data set is downloaded from an outside source, usually in csv format. I may have 10 or 20 columns of data and have had upto 450,000 rows - depending upon the hierarchy/elements, time periods and value measurements I select (pharmaceutical data).
In this example the columns are:
COMPANY_NAME (all competitors within a selected market)
INGREDIENT_NAME
BRAND_NAME
PRODUCT_FORM (tablet, liquid, injection, etc.)
STRENGTH (10mg, 5mL, 50cc, etc.)
TIME_PERIOD (60 months of data)
and then value measures, SALES $, UNITS, RXs, etc.
The data populate rows relevant to each of the column elements. So row 1 may have the corresponding data:
JOHNSON & JOHNSON - ACETAMINOPHEN - TYLENOL - TABLETS - 100MG - JULY 2012 - $100 - 25 units - 0 RX
I go crazy when filtering what is displayed in my pivot table (and eventually reported to management):
1). My results report 50 different companies and I want only the Top 5, BUT, I also want to ALWAYS see COMPANY_X even if they fall below the Top 5... is there a way to flag certain item(s) so it(they) always display?
and
2). Say I'm also reporting the market share for these Top 5 and Company_X. I want to calculate their market share on the (pre-filtered) Total for a market, NOT from the total that is merely the sum of the companies displayed. So even if companies #6 through #25 aren't shown, I don't want their sales contributions deducted from the total - that would overstate the market shares for companies #1 to #5 + Company_X.
Am I missing something in the Pivot table ribbon? Or is it a little more involved?
Thanks for any assistance you can provide - it will save me loads of time and frustration!
P.S. - this is purely icing on the cake but is it possible to have a line item that would give values for "Total Shown" and "Total Others (# of items)"??
Total Market $500
Company 1 $50
Company 2 $40
Company 3 $35
Company 4 $30
Company 5 $25
Company X $10
Total Shown $190
Total Others (19) $310
Bookmarks