How to Bring “Others” in a Pie chart when ranking is applied in the data.
Let’s say there is a set of Region and there Revenue like below:
Region | Revenue |
East Japan | 1,012,727 |
Bavaria | 783,520 |
Mid-West | 441,594 |
South | 400,899 |
West | 324,640 |
East Germany | 259,752 |
West Japan | 33,292 |
East Coast | 19,124 |
Ruhr | 10,976 |
My requirement is to get the TOP 3 Region based on Revenue and to display its percentage share from overall revenue. From below table we want to create a pie chart using Region and Percentage column.
Region | Revenue | Percentage_Share_overall |
East Japan | 1,012,727 | 30.81% |
Bavaria | 783,520 | 23.84% |
Mid-West | 441,594 | 13.44% |
Sum: | 2,237,841 | 68.09% |
Pie chart always display total out of 100, in the above block, the percentage total is 68.09% only.
This will not come in a pie chart properly.
Option will be to show Other’s contribution and show in the pie chart.
For eg.
Region | Revenue | Percentage |
Bavaria | 783,520 | 23.84% |
East Japan | 1,012,727 | 30.81% |
Mid-West | 441,594 | 13.44% |
Others | 1,048,683 | 31.91% |
Sum: | 3,286,524 | 100.00% |
This table can now be properly displayed in Pie chart.
Now comes the interesting part, how to bring value “Others” in the data.
I followed below steps:
Step: 1 Added an object in universe with Select statement as ‘Others’ and pointed it to Region from List of tables. Like below:
Step: 2 In the Webi Report which already has Region and revenue data, created another query selecting the newly created object (Region-Others) and revenue.
Step 3: Merge the object Region and Region-Others.
Step 4: Created four variables:
a. Revenue_variable=If([Region]="Others";[Query 1 (1)].[Revenue];[Query 1].[Revenue])
This variable is used to give data for others in the block along with other regions.
b. Variable1 = [Query 1 (1)].[Revenue]-(Sum([Query 1].[Revenue]) In Block)
This is used to subtract the value of all regions-sum of regions in block, which will assign others data.
c. Variable2=If([Region]="Others";[Variable1];[Revenue-Variable])
This issued to display the values for all regions including “Others”.
d. Final Percentage=([Variable2]/Sum([Variable2]) In Block )
This is used for calculating percentage.
Region | Revenue-Variable | Variable1 | Variable2 | Percentage |
Bavaria | 783,520 | -3,286,524 | 783,520 | 0.24 |
East Coast | 19,124 | -3,286,524 | 19,124 | 0.01 |
East Germany | 259,752 | -3,286,524 | 259,752 | 0.08 |
East Japan | 1,012,727 | -3,286,524 | 1,012,727 | 0.31 |
Mid West | 441,594 | -3,286,524 | 441,594 | 0.13 |
Others | 3,286,524 | 0 | 0 | 0 |
Ruhr | 10,976 | -3,286,524 | 10,976 | 0 |
South | 400,899 | -3,286,524 | 400,899 | 0.12 |
West | 324,640 | -3,286,524 | 324,640 | 0.1 |
West Japan | 33,292 | -3,286,524 | 33,292 | 0.01 |
Step 5: Applied ranking based Revenue_variable. Although I need Top 3 , I selected Top 4(4th for the Others)
Region | Revenue-Variable | Variable1 | Variable2 | Percentage |
Others | 3,286,524 | 1,048,683 | 1,048,683 | 31.91% |
East Japan | 1,012,727 | -2,237,841 | 1,012,727 | 30.81% |
Bavaria | 783,520 | -2,237,841 | 783,520 | 23.84% |
Mid West | 441,594 | -2,237,841 | 441,594 | 13.44% |
Step 6: Using region and Percentage turned it to Pie chart.
So the pie chart is correct now.
Note: If you have query filters in Query 1, apply same filter in query 2 also.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
10 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |