Calculate Customer KPIs - NoOfOrders, AvgOrderSize & CustomerConsumption

[Anonymous]

I have below transactional data, and each row represents product purchased - so if someone bought 5 products, there will be 5 lines for that customer. Basically, all purchases made by one customer (CustID) on one date is one order.

CustID   Product      Date     Qty 

12121     SKU1      2/3/2019   5

12121     SKU2      2/3/2019   2

12121     SKU3      2/3/2019   2

12122     SKU1      2/3/2019   1

So from above Cust 12121 made 1 order of total 9 Qty on 2/3/2019.

Now i would like to calculate some dynamic KPIs - 

Avg Number of Orders (by each Cust)

Average Order Size = Sum(Qty) / Sum(Number of Orders)

Customer Avg Consumption = Total Qty / number of unique customers / number of months

I can do atleast the first 2 KPIs by creating another table aggregating it by orders per cust, and then calculating but then i lose other important fields that i want to use for other KPIs and becomes tricky with the relationship building and all. 

So if someone can help me with calculating these KPIs directly on the given trans data, will be great.

Thank you very much for helping me out.

[v-frfei-msft]

Hi @Anonymous ,

To create measures as below.

Average Order Size = DIVIDE(SUM('Table'[Qty]),COUNT('Table'[CustID]))

Avg Number of Orders = DIVIDE(CALCULATE(SUM('Table'[Qty])),DISTINCTCOUNT('Table'[CustID]))

Customer Avg Consumption = 
DIVIDE([Avg Number of Orders],DISTINCTCOUNT('Table'[Yearmonth]))

[Anonymous]

Thank you very much for your response.

Probably the example i gave was only for one day, so it wasn't clear. Lets consider below example

CustID   Product      Date     Qty 

12121     SKU1      2/3/2019   5

12121     SKU2      2/3/2019   2

12121     SKU3      2/3/2019   2

12122     SKU1      2/3/2019   1

12125     SKU2      3/3/2019   2

12125     SKU3      3/3/2019   2

12127     SKU1      2/4/2019   1

12121     SKU1      2/4/2019   2

The problem with the query is that it is not considering the invoice date. remember all transactions in one day by a customer is only 1 order for that customer.  Your query is valid for one date, but when there is more it does not work.

When you aggregate the above data on Date and CustID it become easier to see this. I added one more column 'NoOfOrders' as all 1s

CustID   Product      Date     Qty  NoOfOrders

12121                        2/3/2019   9                1

12122                        2/3/2019   1                1

12125                        3/3/2019   4                1

12127                        2/4/2019   1                1

12121                        2/4/2019   2                1

And then total 'Number of Orders' is just sum of 'NoOfOrders' for any group of Customers. (Average can be calc the same way)

And i calculated AverageOrderSize measure as

AOS = SUM(Table[QTY])/ SUM(Table[NoOfOrders])

and it works well.

But i dont know how to calc measure CustAvgConsumption though even on this Aggregate dataset plus i lose some info while aggregating, so i wanted to calculate these CRM measures on the original SKU level transaction database.

thanks for your support

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

I can create a calculated table as below. Then what is your excepted result?

Table 2 = SUMMARIZECOLUMNS('Table'[CustID],'Table'[Date],"qty",SUM('Table'[Qty]),"NoOfOrders",DISTINCTCOUNT('Table'[CustID]))