Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |