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:

/wp-content/uploads/2014/01/clip_image002_358342.jpg

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:

/wp-content/uploads/2014/01/clip_image002_358342.jpg

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

/wp-content/uploads/2014/01/clip_image002_358342.jpg

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]

/wp-content/uploads/2014/01/clip_image002_358342.jpg

Hope it helps!!

To report this post you need to login first.

12 Comments

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

    1. Prashant Rangasubhe Post author

      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.

      (0) 
      1. 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.

        (0) 
  1. Arnaud Meunier

    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 ?

    (0) 
    1. 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])

      (0) 
      1. Arnaud Meunier

        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 !

        IncorrectRanking.jpg

        (0) 
        1. Arnaud Meunier

          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

          (0) 

Leave a Reply