Wednesday 4 December 2013

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(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.