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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.