5 Replies Latest reply on Jul 25, 2017 2:51 PM by Kevin Das

# Removing a dimension from the detail without breaking the INDEX formula

Hi all,

I have put together an example of the problem I am having and attached a packaged workbook with some test data.

I think I am close to the solution but I am stuck at the last stage. Not sure if this is because I am missing something obvious or because I have gone about solving the problem in the wrong way!

I want to create a summary table based on the data shown in the chart below. The summary table needs to have one bar per shop for the sum of sales, and one bar for the sum of profit. These bars should show the sum of the 2 products that have the highest sales in that particular shop. The 2 products used for each shop will vary by shop and may change over time so this needs to be dynamic.

My starting point is shown below:

First, I created a combined field (Shop + Product). I used this to sort the products by sales within each shop:

Next, I created an INDEX() formula and used this as a filter to keep the top 2 products per shop:

Finally, I tried to create a summary tab which removes the individual product lines and just has one line per shop. This is where I came unstuck. I can create one line but the bar chart is broken up by the shop + product dimension. If I remove this, it breaks the INDEX formula:

I know that I could remove the label and remove the borders and tooltips. This would give the impression of one bar when you look at the chart, however, I want to be able to show the total sales on a label at the end of each bar as one figure.

I would be grateful for any help to overcome this issue so that the bars are not broken up by product.

All the best

Kev

• ###### 1. Re: Removing a dimension from the detail without breaking the INDEX formula

Does anyone know how this problem can be overcome?

Many thanks!!

• ###### 2. Re: Removing a dimension from the detail without breaking the INDEX formula

Are you looking for this?

Thanks

Deepak

1 of 1 people found this helpful
• ###### 3. Re: Removing a dimension from the detail without breaking the INDEX formula

Hi @Deepak

Thank you for your solution. This definitely provides the solution to the problem I posed.

Unfortunately I simplified the problem a little too much. In reality I will have more products and I just need to total the sales for the top 2 products in each store.

Your solution removes the product with the lowest sales. Do you know how I can retain the sales for the 2 products with the highest sales in each shop?

I have attached an updated workbook which has 4 products. Hopefully any solution which works for 4 products can work for any number of products.

Thanks again

Kev

• ###### 4. Re: Removing a dimension from the detail without breaking the INDEX formula

Hi Kevin,

It is tricky, but you can get this view. Check Screenshot and attached (Sheet 6). Let me know.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 5. Re: Removing a dimension from the detail without breaking the INDEX formula

Hi Deepak

Thank you so much for your help.

I completely forgot about the reference line so it is great to be reminded of this to use for the total label (although, unfortunately, they still come with the horrible tooltips!)

The bars are still separate which is what I had in my original version. I was hoping for one bar for each shop but it looks like this may not be possible. Multiple bars may cause some confusion to the end user if they hover over them.

Definitely an improvement on my initial approach though so thanks again.

All the best

Kev