cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors