Case 6: Correlation analysis

Requirement

In most sales data analysis, the user wants to perform correlation analysis, such as the data analysis of purchasing product A and product B at the same time, including sales amount, sales quantity, percentage of sales, even the association of other more complex conditions, such as customer type, signing time, delivery time, etc., to discover the connection between different products, to match recommendations and sales to promote sales.

In this example, we will take the correlation analysis based on purchased products.

Steps for creating a dashboard

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

2. Add a measure on the table 'FactInternetSales'.

3. Input the expression given below and save it as Value. So, the name of the Measure will be Value.

   countx('FactInternetSales','FactInternetSales'[SalesAmount])

This expression will give the count of the number of rows having non-blank values in the column SalesAmount.

4. Input the expression given below and save it as AllValue. So, the name of the Measure will be AllValue.

    CALCULATE(
    COUNTX(
    SELECTATTRIBUTES(
    'FactInternetSales',
    'DimCustomer'[CustomerKey],
    'DimProduct'[EnglishProductName],
    'FactInternetSales'[SalesAmount]
    ),
    'FactInternetSales'[SalesAmount]
    ),
    summarize(
    FILTER(
    SUMMARIZECOLUMNS(
    'DimCustomer'[CustomerKey],
    'DimProduct'[EnglishProductName],
    "SalesOrderNameCount",
    CALCULATE(
    DISTINCTCOUNTX(
    SELECTATTRIBUTES(
    'FactInternetSales',
    'DimCustomer'[CustomerKey],
    'DimProduct'[EnglishProductName]
    ),
    'DimProduct'[EnglishProductName]
    ),
    REMOVEFILTERS('DimProduct'[EnglishProductName])
    ),
    "NameCount",
    CALCULATE(DISTINCTCOUNTX(ALL('DimProduct'), 'DimProduct'[EnglishProductName]))
    ),

    
            [SalesOrderNameCount] =[NameCount]
        ),
        'DimCustomer'[CustomerKey]
    )
   )

5. Drag both the measures to the Values.