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

    Kevin Das

      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:

      Starting.png

       

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

      Combined.png

       

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

      Filtered.png  Index.png

       

      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:

      Summary.png

      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.

       

      Many thanks for any advice


      All the best

       

      Kev