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 like below:

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(4^th 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.