Xcelsius-Formatting Trick 3- Highlight selection on chart

Point Selected Gets Highlighted
(Click on the images to Zoom)

What we are trying to do here is that, if we have a line graph and if I want to select a point on it to see a particular metric, say its overall percentage, we would like the entire point to be highlighted on the graph
The Dashboard I’ll be using for this particular example is given below. In this Dashboard, I have a Combobox, A Line Chart (Shadow given using the ‘Formatting Trick-1’), a Spread Sheet Table & a set of 5 rectangles required for the 5 products.I could have also used a Combination Chart for this with another line chart over it but this looks far better than that.

You can play with the dashboard below

These are the steps to be followed:
1. First the Data: I have a table which has ‘Employee’ as its rows and products as its columns. What I would like to see in the dashboard is, as I select a particular employee in the Combobox, the line chart should show that employee’s units sold for all the products and as I select a particular product on the line graph, that point should get highlighted and the spread sheet table should show that products units %.

2. Excel Sheet Preparation: On the Excel sheet, we need to do three things. A) Make a Destination for the Combobox such that as a particular ‘Employee’ is selected the Combobox should filter rows and place it in these cells. B) We need to create a table for the ‘Rectangles’ Visibility i.e. as I select a point on the line graph that particular rectangle should become visible. C) A table from where I’ll pick up the particular products units % and Map it to The Spread Sheet table.

a) The Filtered rows Table: There are two things associated with this part, one: this would be the destination of the Combobox after filtering rows and also this part would be the table from where the line graph would pick up its values. So select the Combobox, it picks up its labels’ from column ‘B’ (which is called ‘Employee’ see the picture-1) it’s source data as ‘B3:G8’ and destination as the table lables ‘Filtered Rows’.
b) This is the tricky part: I’ll go to the drill down tab of the line graph and select the series and put the insertion type as ‘Position’. Then put the destination of this as the cell shown in the pic. Below this I have a table which has two columns as ‘Rectangle no.’ and ‘Visibility’. The rectangle no’s are the numbers of the rectangles i.e. rectangle corresponding to product ‘A’ is rectangle 1 and so on. Here I have used an if condition ‘=IF(T4=$U$2,1,0)’ this way the point selected has 1 in the column ‘Visibility’ next to the respective Rectangle number.

c)Then we simply calculate the % units sold using a combination of ‘Index Function’ and ‘Sum’ function and this becomes the which needs to be displayed in the spread sheet table.

3. Once all the connections have been made click preview. In the first picture below ‘Employee’ Selected is ‘David’ and product selected is ‘B’.

In the this image ‘Employee’ Selected is ‘John’ and product selected is ‘D’

  1. gravatar

    # by Anonymous - July 28, 2009 at 12:06 AM

    Hi will you please make video Tutorial, on this one Thank you

  2. gravatar

    # by Connie from Sydney - September 1, 2010 at 8:55 AM

    Good work! You are so smart!

Blog Widget by LinkWithin

Search this blog..