Grouping by Monthly Sales Frequency


#1

Hello Sam,

At this moment, I would need help with a DAX formula related to grouping customers based on the frequency of purchase (how many months a customer buy in a Year), instead of total sales. I have been reviewing many of your videos trying to learn as much as possible about this topic, but I did not find the answer I’m looking for.

The scenario I’m dealing with is as follows:

The company wants to group their customers based on the following criteria:

Customer Category

Consistent Customers = Buying 11 or 12 months within a12 month period prior.
Frequent Customers = Buying 8, 9 or 10 months within a12 month period prior.
Recurring Customers = Buyiing 5, 6 or 7 months within a12 month period prior.
Occasional Customers = Buying 2, 3 or 4 months within a12 month period prior.
Sporadic Customers = Buying just 1 month within a12 month period prior.

I appreciate if you can help me with this.

Thanks in advance for your reply

Lucia


#2

There’s certainly a bit to this one.

Let’s me try to work up an example to solve it.

One question how are you looking to showcase this information.

Is it by year, by month and year, by customer? Understanding the context of the calculation here is very important to the end solution.

Thanks


#3

Thank you Sam for your quick reply.

Attached you can find an example based on how they are creating the report right now. The idea is having the categories summarized by the total number of customers by month/year (including the list of the customers when selected) and of course, using PBI instead of excel :wink:

I hope you can have with this a better idea about what is needed …

Thanks,

Lucia


#4

Ok as mentioned there’s a bit to this, but really good example of the power of DAX.

I’m just building this up slowly to show you my through process.

First I wanted to work out how many purchase per client, just out of interest

Simply

Total Purchases = 
COUNTROWS( Sales )

Then I wanted to work out how many months did each particular client purchase in within a year selection.

Months Purchased In = 
VAR MonthlySales = SUMMARIZE( Sales, Dates[Month & Year],
           "SalesAmt", [Total Sales] )

RETURN
CALCULATE( DISTINCTCOUNT( Dates[Month & Year] ),
    FILTER( MonthlySales,
          [SalesAmt] > 0 ) )

This got me to here.

image

Now I need to find a way to group these customers

And I’m going to use a supporting (secondary table to do this)

This is the quick table I created

image

Now we want to run logic through it to bring it back into our visuals

Customer Group =
CALCULATE( SELECTEDVALUE( ‘Customer Sales Frequency’[Group], BLANK() ),
FILTER( ‘Customer Sales Frequency’,
[Months Purchased In] = ‘Customer Sales Frequency’[Number of Purchases] ) )

So I know that this works now.

I need to make it a little more dynamic now though as we need to be looking back a continuous 12 month time frame for the months.

I’ll come back on this part.

Want to do some more optimization on what I currently have.


#5

Sorry for the delay, there is a bit more to this than I initially thought.

First I updated this formula to be cleaner

Months Purchased In = 
VAR MonthlySales = 
SUMMARIZE( Dates, Dates[Month & Year], "SalesAmt", [Total Sales] )

RETURN
COUNTROWS(
    FILTER( MonthlySales, [SalesAmt] > 0 ) )

Then from here you want to use a formula like this

Purchases Frequency = 
VAR CustomersLast12Months = 
CALCULATETABLE( 
    SUMMARIZE( Sales, Sales[Customer ID], "TotalPurchaseMonths", [Months Purchased In] ),
        DATESINPERIOD( Dates[Date], MIN( Dates[Date] ), -1, YEAR ) )


RETURN
COUNTROWS( 
    FILTER( CustomersLast12Months,
        [TotalPurchaseMonths] = SELECTEDVALUE( 'Customer Sales Frequency'[Number of Purchases], BLANK() ) ) )

This gives you this table

I’ll attach my demo model below

Monthly buying frequency.pbix (518.8 KB)

See how you go with all these ideas.

Quite a tough one, but love the analysis


#6

Thank you so much, Sam !!! It is great learning from someone with this level of knowledge about PBI. Again, I feel blessed for having discovered you and your site :wink:


#7

Hello Sam,

I was trying to complete this assignment following your indications but I’m still struggling with what exactly want to accomplish.

What I would like to do now is grouping the customers iteratively in time, to see their performance by months. The idea is assigning them a category based on the numbers I got after creating the measure of months purchased in:

Months Purchased In = 
VAR MonthlySales = 
SUMMARIZE( Dates, Dates[Month & Year], "SalesAmt", [Total Sales] )

RETURN
COUNTROWS(
    FILTER( MonthlySales, [SalesAmt] > 0 ) )

Based on this, a customer can be changing its category through the time. My first question is if this supporting secondary table would be the correct one to achieve that.

Supporting%20Secondary%20Table

I reviewed again your video regarding the Secondary Table Logic Technique, trying to follow the steps to make work the visuals, but my effort has been in vain … For some reason, it seems like it is not working properly the way did it :frowning:

Customer Performance Group =

CALCULATE( 
     SELECTEDVALUE( CustomerPurchaseCategory[Customer Category], BLANK() ),
         FILTER( ALL( CustomerPurchaseCategory), 
          [Months Purchased In] >= CustomerPurchaseCategory[Min] &&
          [Months Purchased In] < CustomerPurchaseCategory[Max] ) )

Could you please review the logic to see if I missed something?

Thank you as always for your kind help!

Lucia


#8

Ok sure,

Yes so this requires again something different.

The key learning here is the context of this particular calculation is quite different to the last one, because now you want to look at each individual customer.

Really diving into what each context is for any calculation is very very important.

So first I want to setup the table correctly and try to imagine what needs to happen for every individual calc in the matrix.

image

Theorectically at every result we need to work out which group a customer is in looking back 12 months. Then assign the group.

You are correct with the secondary table you’ve created.

First though we need to work out the actual result before we then allocate the appropriate group.

We need to look back a year in every result so we need a formula like this

Months Purchased In = 
VAR MonthlySales = 
CALCULATETABLE(
    SUMMARIZE( Dates, Dates[Month & Year], "SalesAmt", [Total Sales] ),
        DATESINPERIOD( Dates[Date], MIN( Dates[Date] ), -1, YEAR ) )

RETURN
COUNTROWS(
    FILTER( MonthlySales, [SalesAmt] > 0 ) )

Now we have the results we can then run the secondary table logic with exactly the same technique as showcased within the tutorial

Customer Grouping = 
CALCULATE( SELECTEDVALUE( 'Customer Grouping'[Customer Category] ),
    FILTER( 'Customer Grouping',
        [Months Purchased In] >= 'Customer Grouping'[Min] &&
        [Months Purchased In] <= 'Customer Grouping'[Max] ) )

See how you go with these ideas.

Thanks
Sam


#9

Thank you so much, Sam!

Step achieved!

Additionally, I added to the secondary table a category called “lost” for those accounts without activity for one year back since the company considers a lost customer those who don’t purchase from them within a period of 12 consecutive months. Better than expected.

Thank you again :wink:

Lucia


#10

Awesome nice work


#11

Hi Sam,

I’m really having a hard time getting the right figures for the monthly sales based on the grouping I made :frowning:

These are the formulas I used:

Customer Grouping = 
CALCULATE( SELECTEDVALUE( 'Customer Grouping'[Customer Category] ),
    FILTER( 'Customer Grouping',
        [Months Purchased In 12 months back] >= 'Customer Grouping'[MIN] &&
        [Months Purchased In 12 months back] <= 'Customer Grouping'[Max] ) )

  Customer Sales by Group = 
CALCULATE( [Total Net Sales],
    FILTER( VALUES( 'CUSTOMER MASTER'[Customer Name] ),
        COUNTROWS(
            FILTER( 'Customer Grouping' ,
            RANKX( ALL( 'CUSTOMER MASTER'[Customer Name]) , [Total Net Sales] , , DESC ) >= 'Customer Grouping'[MIN] 
            && RANKX( ALL( 'CUSTOMER MASTER'[Customer Name]) , [Total Net Sales] , , DESC) <= 'Customer Grouping'[MAX] )) ))

And this is the table:

image

Apparently, everything was working fine with the formulas and categories, but after a further review, you can see that there are months where I got blanks instead of the category.

I believe that’s the reason why I’m not getting the correct sales amount for each category. For 2017 the total sales are $69.52M but are not matching with the sum of the categories.

Any help would be extremely appreciated

Lucia


#12

Are you sure you’re looking at like for like here.

Just on reviewing this here, to me you have a lot of customers, much more than 12.

But you table only goes to 12 here.

image

But you are ranking customers by total sales here

RANKX( ALL( 'CUSTOMER MASTER'[Customer Name]) , [Total Net Sales] , , DESC )

You’re going to have the majority of your customer ranked about 12 here so you’re missing them completely.

That’s why the totals here are so low.

image

Also I think you will want to place sum zeros into the table here. This will probably fix up your other text results.

image

See how you goes with this and see what changes come about.

Sam


#13

Thank you, Sam, for your reply.

Regarding the table, the ranking is not based on number of customers. The table has been created to assign to customers a category based on the number of months that each particular customer purchased in within the previous12 months from the date selected. The formula I used to know how many months they purchased in is the following:

    Months Purchased In 12 months back = 
    VAR MonthlySales = 
    CALCULATETABLE(
        SUMMARIZE( 'CALENDAR', 'CALENDAR'[Month & Year], "SalesAmt", [Total Net Sales] ),
            DATESINPERIOD( 'CALENDAR'[Date], MIN( 'CALENDAR'[Date] ), -1, YEAR ) )
    RETURN 
    COUNTROWS(
        FILTER( MonthlySales, [SalesAmt] > 0 ) )

This is the result I got from the calculation:

image

From the numbers I got in the Months Purchased in 12 months back table, a category has been assigned to each customer based on the ranking table created …

Now, I need to group the customers and also the total sales based on those categories. My goal is to get something like in these charts:

image

image

I hope this clarifies my point …


#14

yep that’s correct, but you’re formula here is not calculating that.

It’s calculating the ranking of customer based on total net sales, not sales 12 months back.

That’s the issue here I believe


#15

Instead of the RANKX here you likely need to place in here the measure for - Months Purchased In 12 months back

See here for further ideas on this


#16

Thank you, Sam, for your reply.

I saw your videos Dynamically group Customers by Ranking; Dynamic Grouping via support tables; grouping via lookup table logic and grouping via calculated columns but still I haven’t been able to resolve my issue.

I feel I’m close on what I need, but still, I cannot find the proper formula to achieve it since my knowledge about DAX is still kind of limited :frowning:

I was able to get the counts of months that I need for the 12 months back and also the categories associated to those counts, but there is still the final step missing in my calculation.

Months Purchased In 12 months back = // Number of months that each particular client purchased in within the last 12 months from the date selected.
VAR MonthlySales = 
CALCULATETABLE(
    SUMMARIZE( 'CALENDAR', 'CALENDAR'[Month & Year], "SalesAmt", [Total Net Sales] ),
        DATESINPERIOD( 'CALENDAR'[Date], MIN( 'CALENDAR'[Date] ), -1, YEAR ) )
RETURN 
COUNTROWS(
    FILTER( MonthlySales, [SalesAmt] > 0 ) )

Customer Grouping = 
CALCULATE( SELECTEDVALUE( 'Customer Grouping'[Customer Category] ),
    FILTER( 'Customer Grouping',
        [Months Purchased In 12 months back] >= 'Customer Grouping'[MIN] &&
        [Months Purchased In 12 months back] <= 'Customer Grouping'[Max] ) ) 

Now, I would need to find the formula to dynamically group those categories based on the total number of customers for each of the categories. That is, how many of those customers are within every category during the period selected, taking into consideration that “Months Purchased in 12 Months back” and “Customer Grouping” are both measures, not calculated tables …

I really need your help on this …

Thank you in advance for your support

Lucia


#17

Isn’t is just the below?

Customer Sales by Group = 
CALCULATE( [Total Net Sales],
    FILTER( VALUES( 'CUSTOMER MASTER'[Customer Name] ),
        COUNTROWS(
            FILTER( 'Customer Grouping' ,
               [Months Purchased In 12 months back]  >= 'Customer Grouping'[MIN] 
            && [Months Purchased In 12 months back] <= 'Customer Grouping'[MAX] )) ))

See how you go with this


#18

Hi Sam,

Yes, that is giving me the total sales by category, but now, I have a problem … Since I’m calculating the number of months a customer purchased in for the past 12 months, I have cases like customer 23 that since the date of the last purchase was on 2016 I’m getting blanks if I select, for example, 2017.

image

Is there any way to fix this?

Thank you again for all your help …


#19

I replied over here

https://forum.enterprisedna.co/t/months-purchased-in-12-months-back/1651/3