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
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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors