# Pie Chart with Ranking and Others in Web Intelligence

Pie Chart with Ranking and Others in Web Intelligence:

Pie chart always display total out of 100. And if you have ranking (that is restricting few rows) then it will not depict the actual or correct data unless you add the rest of ranking fact/numbers (Others) to the chart.

Let’s take sample data:

Requirement is to show top 3 Ethnicity, Percentage in a 100% pie chart.

For calculating percentage I created a measure variable with the following definition

PercentageVar =[Total]/NoFilter(Sum([Total]))In Report

After applying top 3 ranking:

Note: Applied percentage formatting to Percentage column

As you see the total percentage is not 100%. That’s the reason business requested for adding others and make it 100%.

If i create the pie chart on this above it will not consider the “Others” contribution. In order make pie chart 100% it is necessary to bring “Others” into consideration like

Steps to follow:

Create measure variable

RankVar =Rank([PercentageVar];[Ethnicity]) In ([Ethnicity])

Create detail variable by associating dimension [Ethnicity]

TopVar =If([RankVar]<4;[Ethnicity];”Others”) In ([Ethnicity])

Create pie chart with [TopVar] and [PercentageVar] variables.

Apply descending sorting on [PercentageVar]

Hope it helps!!

### Assigned Tags

You must be Logged on to comment or reply to a post.

hi i am unable to do sorting @ percentage variable

Well it is working in BO 4 version. I have an alternative too if it is not sorting correctly.

If PercentageVar is giving 100% for all rows then use the below instead of the one i mentioned in the blog.

PercentageVar =Sum([Total])/NoFilter(Sum([Total]))In Report

Bring TopVar and Total in a table.

Apply descending sorting on Total column.

Create alerter as [Total] = [Total] then display [PercentageVar]

Now convert the table to pie chart.

Will drill down feature on this scenario. We are looking for similar solution but we drill down on the chart pie except on Others. Please let us know if that works on your end.

Is it possible for the attributes to show when you hover over each category except for "Other" and not use the legend key?

Awesome. Thank you.

You are welcome Tom!

I get IT.

thank you.

You are q good man.

Awesome.

thanks for sharing this note. Prashant ðŸ™‚

Srini

Hello, is this working in Bo 4.0 also ?

I am trying to do this with values, instead of percentages.

Customer    Value

A               20

B               30

C               10

D                50

E               100

F                25

1) I want to create Ranking on my table by Value, showing the Top 15

In my case, I right clicked the Value List, and Added a Top Ranking of 15

2) I created a Rank measure to show ranking number as follow:

=Rank([SingleMonth].[Market Chargeable Weight])

2) I followed the steps above to create the Others detail variable:

=If([Agent Ranking (Chargeable Weight)]>15;[Agent Head Office Name];"Others") In ([Agent Head Office Name])

If I drag and drop this detail into the Agent Head Office Name column, then I'm getting #MULTIVALUE as a result, and I'm not sure how the value can appear then besides the detail

I simply want "Others" to be added to the customer name column and the value of those others into the Value Column, after the top results.

In the above that would be:

Rank     Customer     Value

1          E                        100

2          D                        50

3         B                         30

Others                 55

And to generate a simple pie chart from this that would show the right results.

Can you please let me know if I'm missing something there ?

Have you got your Rank measure wrong?

The original post says create these two

Create measure variable

RankVar =Rank([PercentageVar];[Ethnicity]) In ([Ethnicity])

Create detail variable by associating dimension [Ethnicity]

TopVar =If([RankVar]<4;[Ethnicity];"Others") In ([Ethnicity])

=Rank([SingleMonth].[Market Chargeable Weight])

so is missing a

Hello Andrew,

I applied the Ranking directly with the existing Top Ranking feature (Right click on the list of values, Ranking, Top 15)

The Rank variable I set up is just to show the list.

If I follow the above, I should create the Ranking formaula as follows