cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MLemanski
New Member

Purchase Frequency Stratification

Hello,

 

I am trying to solve the following problem in PowerBI.  I am analyzing customer purchase frequency, so I created a dynamic measure that calculates how many unique months a customer purchases an item in over a period of time.  I then divide that by the months that are in that period to come up with a Customer Purchase Frequency.  

 

[Customer Purchase Frequency] = [Unique Months Customer Purchased] / [Months in Selection]

 

This results in a percentage that I can then put into buckets, no matter the length of the period selected.  For example:

 

Purchase FrequencyMinMax
0 - 3 Months75.0%100.0%
3 - 6 Months50.0%75.0%
6 - 12 Months8.3%50.0%
12+ Months0.0%8.3%

 

What I would like to do now is summarize my sales, unique customer count, etc. by my "Purchase Frequency" buckets as defined above.  The issue I am having is that since my "Customer Purchase Frequency" is created from a measure, I can't use it or my buckets based off it as an axis on any charts or tables.

 

I believe the solution has to do with using a disconnected table, formatted like the table above, but I am not sure how to get my sales, customer counts, and other calculations to work with it.  Any direction would be much appreciated.  Also, if there is a simpler solution to this problem, please let me know.

 

Thanks!

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @MLemanski

If you want the "Customer Purchase Frequency" to be used in axis, you could create a calculated column with theis formula.

If you want to get a table using the column from your original table, you could consider the dax function Summarize.

 

 

Best Regards

Maggie

 

Hi Maggie,

 

My issue is that the Customer Purchase Frequency is a dynamic calculation.  A customer's purchase frequency may be "0-3 Months" for one category of product, but "6-12 Months" for another category.  When I try using a calculated column, it just gives me an overall frequency, regardless of how I want to filter it.

 

I have my data in the standard format, with "Sales Data" table connected to a "Customer" table, "Item" table, and "Calendar".

 

In PowerPivot, my work around was creating a pivot table that listed each customer and calculated their purchase frequency and sales based on which item category slicers I selected.  I then referenced that table using Excel's native CountIfs or SumIfs functions to summarize them into the buckets that I wanted based off the logic in my original post.

 

I hope this adds some clarity for what I am looking for!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors