showing values in %

[#1]

Hi,

i have a stack bar chart wherein the values are alreday converted and displayed in $,mm Format and finally shown without the M symbol (eg: 1500).

To explain things more clearly, i have 4 regions on x-axis and there are designations grouped as stacked.

For the first bar, the numbers shown for the first region (Eg: Asia) are as follows:

Director: 1500

VP: 1300

AVP: 800

svp: 500

i want to convert them in % so that the total of it should sum up t0 100%.

For director it should show 37% (1500/4100)

For VP it should show 32% and so on

Hope the explanation is clear enough

Really looking forward for a solution to this since i have my data always in million dollar and would like to show convert them into % format even while using a pie chart.

Thanks

[swuehl]

Use the TOTAL qualifier in your denominator aggregation:

=sum(Sales) / sum(TOTAL<Region> Sales)

where Region is your first dimension field and Sales the field you aggregate.

Then use the number tab of the chart properties to format as percentage.

[stevedark]

Hi Yousef,

Typically you would do that with the TOTAL function, so if your expression is sum(Value) percentage would be:

sum(Value) / sum(TOTAL Value)

Where the TOTAL causes the divisor to calculate across all dimensions.  If you have two dimensions in your chart (say in a Pivot) you can do the total across a second dimension:

sum(Value) / sum(TOTAL <Dimension> Value)

If you want to ignore selections on a certain field (so you always look at the total, not the total of selected items) you can use set analysis to ignore some selections in the total:

sum(Value) / sum({<Dimension=}>} TOTAL Value)

Hope that helps,

Steve

[#4]

Hi,

Chart Properties ->Expressions Enable the Relative Check Box .

[#5]

Thanks for the promt reply. This worked. The only problem that i face is that the data labels are missing in some places. For eg: if there are 4 desgignation for a region, it shows data labels only for 3 designations.

Any idea why that is happening. On mouse over, it shows the value as 8% but that is not displayed on the bar.

Thanks

[preminqlik]

use relative in chart proprties > expression tab

[stevedark]

HI Yousuf,

Can you upload a screenshot of this?  Or if you have dummy data in there the document itself.

Cheers,

Steve

[nico_ilog]

Excellent. What a great help!

[Anil_Babu_Samineni]

Hello Yousuf,

Did you get any solution for this. Because, I am having same issue...