Pareto analysis and the 80/20 rule in self-service business intelligence

Quite often we will use Pareto analyses to quickly isolate or highlight the elements that matter. Though never scientifically proofed, Pareto refers to the 80-20 rules described as 20% of the causes determine 80% of the problems. In practice it has been a very efficient way of quickly analyzing and judging data.

At the end of this article is an instruction video showing you how to create the pareto analyses

I will guide you through the simple but strict process of creating a Pareto analysis with self-service business intelligence. Especially the cumulative sum function and the "% cumulative sum of total" are you key handles here.

Pareto analysis

is a formal technique useful where many possible courses of action are competing for attention. In essence, the problem-solver estimates the benefit delivered by each action, then selects a number of the most effective actions that deliver a total benefit reasonably close to the maximal possible one.

Pareto analysis is a creative way of looking at causes of problems because it helps stimulate thinking and organize thoughts. However, it can be limited by its exclusion of possibly important problems which may be small initially, but which grow with time. It should be combined with other analytical tools such as failure mode and effects analysis and fault tree analysis for example.[citation needed]

This technique helps to identify the top portion of causes that need to be addressed to resolve the majority of problems. Once the predominant causes are identified, then tools like the Ishikawa diagram or Fish-bone Analysis can be used to identify the root causes of the problems. While it is common to refer to pareto as "80/20" rule, under the assumption that, in all situations, 20% of causes determine 80% of problems, this ratio is merely a convenient rule of thumb and is not nor should it be considered immutable law of nature.

The application of the Pareto analysis in risk management allows management to focus on those risks that have the most impact on the project

In this Let Me Guide You series we will show you how to create a Pareto analyses using SAP Lumira. Below is the step-by-step

Start position is an example data set that – amongst others – composes of Order numbers and Actual revenue. As a data-analyst I quickly want to see what orders make 20% of the total revenue and what orders generate 80% of the Actual revenue.

  1. Duplicate your Actual measure and rename it to for example “RevenueParetoTotal”

  2. Create a crosstab with Year as dimension and RevenueParetoTotal as measure. This gives us the total actual revenue of all orders

  3. Export to a separate dataset or to a separate xls(x)

  4. Import the separate dataset or xls(x) back into your analysis

  5. Join the original set to the new added dataset (with RevenueParetoTotal) over the Year-dimension

  6. Create a calculated measure “RevToParetoPerc” defined as {Actual / RevenueParetoTotal} and set displayformat to percentage %

  7. Create a predefined calculation as Running Sum on RevToParetoPerc

  8. Create a combined Column – Line chart with Actuals in descending order and your predefined running sum calculation and use Orders as dimension

tweet this article

Share on Facebook

Highlighted articles