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.
Nice blog with proper explanation
Thanks. Well explained and very useful.
Thanks. Helpful and easy to understand post.
Very nice post, really helpful. nicely explained with example and formulas.
Stellar post -> already made a working version in my company!
Question: how can we do the pareto of pareto? To filter out the first 3 results and affect them with other objects?