Case 7: Pareto analysis

Requirement

For most sales and retail industries, it is common for a small number of products to contribute most of the value.

For example, product A sells 10 percent of the total, but the sales are 80 percent of the total sales. So, users want to analyze and find this 10% of products to maximize the use of resources in daily marketing and management.

In order to find the best-selling products, users want to calculate the cumulative sales and cumulative percentage for each product.

According to the cumulative ratio, the products are divided into several types, such as general sales: '0~70%', good sales: 70~90%, excellent sales: 90%~100%.

In addition, this analysis may be based on different latitudes, such as different regions or different years.

Steps for creating a dashboard

1. Drag a Pivot table and drag Product Name to Rows, SaleAmount to Values.

2. Add a measure in table 'CorrelationAnalysis', and add the following expression as below. Name the measure as MeasureRange:

SWITCH(
    TRUE,
    sum('CorrelationAnalysis'[SaleAmount]) < 10000, 
        "less than 10k",
    sum('CorrelationAnalysis'[SaleAmount]) >= 10000 &&          sum('CorrelationAnalysis'[SaleAmount]) < 30000, 
           "10k-30k",
    sum('CorrelationAnalysis'[SaleAmount]) >= 30000 && 
    sum ('CorrelationAnalysis'[SaleAmount]) < 50000, 
            "30k-50k",
    sum('CorrelationAnalysis'[SaleAmount]) >= 50000 &&          sum('CorrelationAnalysis'[SaleAmount]) < 100000, 
            "50k-100k",
    sum('CorrelationAnalysis'[SaleAmount]) >= 100000,                     "greater than 100k"
 )

3. Bind the MeasureRange to Values.