3 Replies Latest reply on Aug 8, 2012 12:08 PM by Kevin Hogan

# Market Share Analysis - Part of a Whole

I am trying to create a sheet that has a week to week view of market share for different retailers.  I have attached an image below (not sure I can pass the data from the work book in whole).  The idea is to have the gray line at the top stay the same, it is a count of all the total sales for all of the retailers.  The goal would be to be able to filter that line by other fields like product category or brand and then have a filter that affects just what brands show up on the market share.

For example:  Let's say I want to select a different brand so the sales line will look different (lets say drop 10K units or so).  I want the percent of total the retailers have to change too.  Then if I want to eliminate retailer 1 and 2 and just see the lines for 3-10 I can.  I can try and dummy up the workbook to help and I'll clarify wherever necessary.

The objective is to show market share of each retailer.  The selecting and unselecting of retailers can help simplify the graph.

• ###### 1. Re: Market Share Analysis - Part of a Whole

Hi Kevin,

So should the gray line remain constant regardless of what is being filtered out? Or the value should also change?

-Tracy

• ###### 2. Re: Market Share Analysis - Part of a Whole

Tracy,

This is a great question and as I went to answer it I think I'm beginning to see my problem.

I need the gray line to be filtered for everything EXCEPT the retailer.  I tried to set up almost like a dummy retailer filter with a LOOKUP(min([Customer],0) and then putting that in the filter which works...unless I filter out the minimum value, then the whole graph dissapears.

I'm not sure what I'm trying to do is even possible / logical.  Basically want the colored lines to represent a table calculation of what percentage each retailer has a market share.  The filter for those would only effect which lines are shown.  The other filters in the sheet would change the value of the total sales which then would change the table calculation of market share.

Thanks,

Kevin

• ###### 3. Re: Market Share Analysis - Part of a Whole

Working on uploading a .twbx today for better explanation.  Thanks for the interest!