cancel
Showing results for
Did you mean:
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 Frequency Min Max 0 - 3 Months 75.0% 100.0% 3 - 6 Months 50.0% 75.0% 6 - 12 Months 8.3% 50.0% 12+ Months 0.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
Community Support

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

New Member

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!

Announcements

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

#### Microsoft named a Leader in The Forrester Wave

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

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

Top Solution Authors
Top Kudoed Authors