Grouping customers evenly by rank

DAX DAX Calculations
1

I’ve poured through all the customer grouping forum posts and videos and I can’t seem to hone in on this one at a beginners level. I have a list of 5,100 customers ranked based on total sales. I need to evenly group the customers by rank into 10 groups. So, group 1 would have the first 510 customers, group 2 would have the second 510 and so on until I have 10 groups of 510 customer each based on their sales rank.

After reviewing a lot of the great customer segmentation material I just can’t seem to wrap my head around the best way to accomplish this.

2

@mclark,

There are a lot of different ways to handle this one, but I would use one of my favorite tools here - SWITCH ( TRUE() ). Below is a really simplified example with 11 customers in three groups:

    Sales Rank Group = 

    VAR TotalCust = COUNTROWS( Data )
    VAR CustPerGroup = 4

    RETURN 
    CALCULATE(
        SWITCH (TRUE(),
            [Sales Rank] <= CustPerGroup, 1,
            [Sales Rank] <= (CustPerGroup * 2), 2,
            3
        ),
        VALUES( Data[Customer ID] )
    )

image

https://forum.enterprisedna.co/t/using-switch-true-logic-in-power-bi-dax-concepts/488

Hope this is helpful. Full solution file attached below.

3

Have you gone through this tutorial below as I believe it answer you exact query…

This enables you to create dynamic groups by a ranking.

Sam

4

@BrianJ @sam.mckay
Thank you so much for providing these solutions. They do both work well when I know the set number of customers within the stated example. However, our number of customers changes over time and I think I’m having the most trouble with how to get the grouping to change with that number. For instance, in 2018 we may have had 6,100 customers so each group would then have 610 but if we slice to 2019 we may only have the 5,100 customers and so on. How do I get it to dynamically change with the data set? I’ve tried the dynamic grouping method with a supporting table of percentages but to no avail.

5

Yes ok to do this, you need to focus on making your min and max details dynamic in the supporting table you are creating.

With some simple logic you want to setup a calculated column in those tables rather than hard coding them like they are in the examples.

Once you’ve sorted this then you can use the exact formula technique using DAX as per above.

The calculated column shouldn’t be to difficult, but you might have to play around and experiment with maybe some SWITCH logic.

Sam

6

Hi All,
I’ve been able to achieve this in Excel but am having a hard time with the context in BI yet. I’m attempting to do two things:

  1. Identify a customer as a Grower, Decliner or Defector based on YoY sales.
  2. Group these customers in even groups of 10 by their yearly rank.

I’ve been able to achieve the customer growth definition based on a measure but it’s not dynamic. I’ve attempted to make a slicer but haven’t had luck. From there, I have achieved the even grouping within the Customer Table itself but again, it’s not dynamic. Any help getting me to the finish line in the attached demo set?
eDNA Forum - Customer Decile Testing.pbix (150.8 KB)

7

@mclark,

I made a couple of necessary minor changes first (marked your Dates table as a date table, altered your total customers measure to incorporate ALLSELECTED from the Year slicer), but here’s the measure that’s driving the dynamic decile calculation:

Decile Grouping SWITCH = 
VAR CustDec = [Customers Per Decile]
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
CALCULATE(
    IF( [Sales Rank] = BLANK(),
        BLANK(),
        SWITCH ( TRUE(),
            [Sales Rank] <= Max1, 1,
            [Sales Rank] <= Max2, 2,
            [Sales Rank] <= Max3, 3,
            [Sales Rank] <= Max4, 4,
            [Sales Rank] <= Max5, 5,
            [Sales Rank] <= Max6, 6,
            [Sales Rank] <= Max7, 7,
            [Sales Rank] <= Max8, 8,
            [Sales Rank] <= Max9, 9,
            10
        )
    ),
    VALUES( Customers[Customer ID])
)

image
image.png1039×560 55.4 KB

There are some small “fiddly” decisions built into this analysis that you’ll want to take a closer look at (e.g., how blank values are treated, whether for small sample sizes you use ROUND, ROUNDUP or ROUNDDOWN in calculating customers per decile, whether someone with no sales in this year or last should be considered a “Defector” or not, etc.), but if you want handle any of these issues differently, altering the DAX code to do so should be pretty straightforward given the measure branching approach used.

I hope this is helpful. Full solution file attached below. Good luck with the project.

1 Like