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!!
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])
Your measure variable is
=Rank([SingleMonth].[Market Chargeable Weight])
so is missing a
In ([Agent Head Office Name])
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 !
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 ?