Skip to Content
Author's profile photo Former Member

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.
      Author's profile photo JEET PANDEY

      hi i am unable to do sorting @ percentage variable

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo RUC ..
      RUC ..

      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.

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Tom Munson
      Tom Munson

      Awesome. Thank you.

      Author's profile photo Former Member
      Former Member

      You are welcome Tom!

      Author's profile photo wang zhanhui
      wang zhanhui

      I get IT.

      thank you.

      You are q good man.

      Author's profile photo Former Member
      Former Member


      thanks for sharing this note. Prashant 🙂


      Author's profile photo Former Member
      Former Member

      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 ?

      Author's profile photo Andrew Dale
      Andrew Dale

      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])

      Your measure variable is

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

      so is missing a

           In ([Agent Head Office Name])

      Author's profile photo Former Member
      Former Member

      Hello Andrew,

      Thanks for your feedback.

      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

      =Rank([SingleMonth].[Market Chargeable Weight];[Agent Head Office Name])In([Agent Head Office Name])

      But this does not give me the correct ranking (see attached image).

      On the left, the ranking applied with my measure, and the Rank2 is the one applied with this formula.

      Note that the list of values is filtered at multiple levels through prompts, and I believe this ranking formula does not pick up the filter while mine does.

      Any idea from there so that I can process the second formula and make it work ?

      Thanks !


      Author's profile photo Former Member
      Former Member

      If I remove my ranking just to keep the other formula, it still does not work and duplicates ranking with no reason (same rank # will show for different customers/values). I believe it is ranking based on the name and not the value ?

      Rank2second step.jpg