Pareto chart in Web Intelligence with target (80%) line
In this article, I will be providing step by step detail about how to create a Pareto chart in Web Intelligence with target line. It is one of the most common type of chart used in Statistical Analysis for decision-making.
Pareto chart represents the Pareto Principle called 80/20 rule. It means that 80 % of the results are due to the 20% of the causes. For example, 20 % of the customers contribute to the 80% of sales. Note that in most cases you wouldn’t find the numbers to be exactly 20% or 80% but this has been theoretically correct.
This blog is specifically about how to add a target line that can be used as an indicator for easily identifying the 80 % factor.
Overview: In this blog, I will be creating a Pareto chart to list out the cities those are contributing to 80% of the sales among other cities. A bar has been added in the graph for easily identifying the cities those are contributing towards 80% of the total sales.
BusinessObjects version: BI 4.1 SP06
1: Create a new Web Intelligence report and add [City] and [Sales] in report. Sort the sales in descending order as shown below:
2: Add a new column and assign a variable named [Cumulative Sales]. In order to get cumulative sales, we will be using the function =RunningSum() as shown below:
[Cumulative Sales]= RunningSum( [Sales])
3: Add new column and create a new Variable as [Cumulative % Sales]. To achieve cumulative percentage, use formula as mentioned below:
[Cumulative % Sales]=(RunningSum([Sales])/ (RunningSum([Sales]) in Block))*100
4: As per the requirement, we need to add a line at 80% of the sales as an indicator for ease of analysis. It will give set of the cities those, who are contributing to the 80% of the total sales. Create a new variable [80%] with definition as shown below:
[80%] = If ( Previous([Cumulative Sales])< ([Cumulative Sales] In Block)*0.8 And (( ([Cumulative Sales] In Block)*0.8 )<[Cumulative Sales])) Then (([Cumulative Sales] In Block)*0.8) Else 0
Note: Now our data set for Pareto chart is ready.
5: Add a new column chart of type “Combined column line chart with 2-y Axis”, and assign category axis and Value axis as shown below:
6: Right click on the chart and navigate to the Measure Properties. Assign the chart type for measure as shown below:
7: Now, Pareto chart is ready with target line of 80%. We can see that Chicago and Raleigh are contributing towards the 80% of total sales.
Hope this will help you implementing Pareto chart with target line in Web Intelligence.