Reversed Decile Grouping DAX Formula

DAX Formula for Reversed Decile Grouping

The following DAX formula calculates the Reversed Decile Grouping based on total customers and sales rank:

Reversed Decile Grouping A1 = 
VAR CustDec = ROUND(DIVIDE(CALCULATE([Total Customers A1], ALLEXCEPT('RPBIExtract', 'RPBIExtract'[MSISDN])), 10, 0), 0)
VAR SalesRank = [Sales Rank]  

VAR Max1 = CustDec * 1
VAR Max2 = CustDec * 2
VAR Max3 = CustDec * 3
VAR Max4 = CustDec * 4
VAR Max5 = CustDec * 5
VAR Max6 = CustDec * 6
VAR Max7 = CustDec * 7
VAR Max8 = CustDec * 8
VAR Max9 = CustDec * 9
VAR Max10 = CustDec * 10

RETURN
IF(ISBLANK(SalesRank),
    BLANK(),
    SWITCH(TRUE(),
        SalesRank <= Max1, 10,
        SalesRank <= Max2, 9,
        SalesRank <= Max3, 8,
        SalesRank <= Max4, 7,
        SalesRank <= Max5, 6,
        SalesRank <= Max6, 5,
        SalesRank <= Max7, 4,
        SalesRank <= Max8, 3,
        SalesRank <= Max9, 2,
        1  
    )
)

Explanation of the Formula

1. Variables Declaration:

   • CustDec: Calculates the total number of customers divided into deciles. The use of ALLEXCEPT ensures that the grouping is done for each unique MSISDN.
   • SalesRank: Captures the current sales rank of a customer for later comparisons.

2. Max Variables:

   • Max1 to Max10: Represents the thresholds for each decile from 1 to 10, calculated as multiples of the customer deciles. Essentially, these variables segment customers into ten equal parts based on their rankings.

3. Return Statement:

   • The IF function checks if SalesRank is blank, returning a blank result if true.
   • The SWITCH function evaluates multiple conditions. Depending on how SalesRank compares to the maximum values, a corresponding decile number from 1 to 10 is returned. The lower the SalesRank, the higher the decile number, which is why it’s referred to as "Reversed Decile Grouping."

Practical Example

• Context: Assume you have a dataset with customer sales where Total Customers A1 is a measure counting the total number of customers, and Sales Rank is derived from the sales performance of those customers.

• Usage:

  • Apply this formula in a calculated column or measure to categorize customers into deciles whereby a lower sales rank results in a higher decile number.
  • For instance, if the sales rank of a customer is 15 and the calculated deciles indicate that there are 100 total customers, approximately each decile would hold 10 customers. Thus, a customer with a rank of 15 may fall into decile 8.

Conclusion

The provided DAX formula effectively implements reversed decile grouping, allowing for stratification of customer data based on sales performance. This can be particularly useful for analysis and decision-making processes within business intelligence applications. For further mastery in DAX and practical applications, consider exploring the Enterprise DNA Platform.