Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculating Order Frequency by Customer

I am attempting to create a measure or series of measures that will allow me to do the following steps:

 

  1. Calculate time between each order placed by a customer
  2. Calculate the avg time between orders per customer
  3. 3) Break the data into quintiles

*Must retain the context of slicers/filters on the page

 

Currently I am doing this via a SQL query that returns a summary table with the quintiles already calculated.

 

I would like to do this in Power BI so I can filter the query without using parameters.

 

My data set includes the following tables

  1. FactLine (transaction lines)
  2. DimDate
  3. DimProduct (product details)
  4. DimCustomer

These tables are related to FactLine. How can I calculate the average order frequency by customer then break it out into quintiles? Thanks in advance for all help.

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    Could you show the summary table with the quintiles already calculated for me to refer to?

     

    As for some details, please confirm:

    1.Calculate time between each order placed by a customer 

    eg.

    customer1     order   date

    1                     1         2018/1/1

    1                     2         2018/1/7

     

    for customer1, time between each order =2018/1/7-2018/1/1=6 days, right?

     

    2.Calculate the avg time between orders per customer

    for customer1, avg time between orders=6days/2 orders=3, right?

     

    3.Break the data into quintiles

    ???

     

    Best Regards

    Maggie