Pie Chart % issue and Radio buttons

[Former Member]

Hi,

I have a report that sales by City. There are 200 cities and sales % per city is shown in desceding order. As 200 cities I had difficulty to show In pie chart, I showed top 30 cities which account for nearly 70%. The issue is Pie chart calculates one more % based on values in selection and shows two different %s. one from Excel and another one that is calculated based on top 30 cities and thsi si not accepatble. How can i get rid of this new % that is aclculated based on values in selection?

2. I want to give option to users to select either top 30 or all. How can I give should I use radio buttons? Any example xlf file?

Plese help me

BR, Nanda Kishore

[Former Member · Accepted solution]

hi,

The option you can try out is

Appearance -> Text -> Data Labels (specify only 'Value' for 'Label Contains')

As we don't have control on how Mouse-Over values should be displayed.

Please Uncheck 'Mouse-Over Values'

This will not show new %.

For 2nd question, Pie chart is not suitable when data points are more..

So, you can select a Column Chart and enable Range Slider... such that user can focus on required data only by narrowing the

range in slider.

Regards,

Vamsee

[Former Member · Accepted solution]

Hi,

I have a column chart also which shows Absolute numbers where as Pie Chart shows %. So I need to use pie chart only. Requirement is to give option as explained in point2. This is a kind of must, so could you please helpme on how this can be implemented.

I am told Radio button feature is helpful Not sure how to implement this.

Thanks in advance.

Nanda Kishore

[Former Member]

I have to agree with Vamsee as I can't see 200 data point on a pie chart working. However if it is a must I can think of at least two possible solutions using the radio button or any other selector type component such as a list box, combo box or label based menu.

In both instances add a radio button with 2 options - One for "Top 30 Cities" and one for "All Cities". Set the Insertion type to Label and the Destination cell to any cell (For this example I'll assume cell $A$1 although this will probably be different in your model)

Solution 1: Create 2 pie charts, one which shows the top 30 cities and one that shows all cities. Using dynamic visibility set the top 30 cities chart to show when cell $A$1 is "Top 30 Cities" and set the all cities chart to show when cell $A$1 is "All Cities"

Solution 2: Assuming the cities names are in the range $B$1:$B$200 and the values are in $C$1:$C$200 with the top 30 cities in $B$1:$B$30.

In an empty column ($D in this example) for the cells $D$1:$D$30 use the formula '=$C1' (copied down so it reads =$C2 in cell $D$2, =$C3 in cell $D$2 and so on).

In the cells $D$31:$D$200 use the formula '=IF($A$1="All Cities",$C31,"")' (again copied down so it reads '=IF($A$1="All Cities",$C31,"")' in cell $D$31, '=IF($A$1="All Cities",$C31,"")' in cell $D$32 and so on.

Bind your pie chart labels to cells $B$1:$B$200 and bind the values to Sheet1!$D$1:$D$200. On the Behaviours Tab make sure that Ignore Cells at End-of Range only In values is ticked.

This solution has the advantage of only using 1 chart however may be very marginally slower due to excel formula.

Hope this helps.

Paul

[Former Member]

Hi,

I have two charts Line and Pie Charts. Both should be dynamically change based on Radio button. How can I achieve this.

Regards,

Nanda Kishore

[Former Member]

hi,

Using "Dynamic Visbility" feature you can achieve this.

Let say you have 2 radio buttons 'Line' and 'Pie'.

Radio Button -> Data Insertion -> Insertion Type : Position

For 1st item selection, 1is inserted into a cell.

For 2nd item selection, 2 is inserted into a cell.

Select the Line Chart -> Properties -> Behavior -> Dynamic Visibility ->

Status -> map to the cell.

Key -> 1

Select the Pie Chart -> Properties -> Behavior -> Dynamic Visibility ->

Status -> map to the cell.

Key -> 2

Regards,

Vamsee