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.
