Pareto chart in Web Intelligence with target (80%) line

Hi All,

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

Implementation steps

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.

Thanks,

Manveer Nakoti