Forum Discussion

MLemanski's avatar
MLemanski
New Member
8 years ago

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 purchas...
v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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

 

  • MLemanski's avatar
    MLemanski
    New Member
    8 years ago

    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!