1. How would you identify customers whose average order value is increasing month over month?
Calculate Average Order Value (AOV) and compare it with the previous month.
(
SUM([Sales]) / COUNTD([Order ID])
)
>
LOOKUP(
SUM([Sales]) / COUNTD([Order ID]),
-1
)
This identifies customers whose spending per order is growing.
2. How would you calculate the percentage of profitable orders?
Count profitable orders and divide by total orders.
COUNTD(
IF [Profit] > 0
THEN [Order ID]
END
)
/
COUNTD([Order ID])
This measures order quality rather than revenue volume.
3. How would you identify products that contribute more than 10% of category sales?
Compare product sales against category sales using FIXED LOD.
{ FIXED [Product Name] :
SUM([Sales])
}
/
{ FIXED [Category] :
SUM([Sales])
}
> 0.10
This helps identify key products within each category.
4. How would you calculate the average time between a customer's first and last purchase?
Calculate the duration of the customer relationship.
DATEDIFF(
'day',
{ FIXED [Customer ID] :
MIN([Order Date])
},
{ FIXED [Customer ID] :
MAX([Order Date])
}
)
This is often used in customer lifecycle analysis.
5. How would you identify categories whose sales are growing faster than the company average?
Compare category growth rate against overall growth rate.
Category Growth:
(
SUM([Sales])
-
LOOKUP(SUM([Sales]),-1)
)
/
ABS(LOOKUP(SUM([Sales]),-1))
Compare with Overall Average Growth:
(
SUM([Sales])
-
LOOKUP(SUM([Sales]),-1)
)
/
ABS(LOOKUP(SUM([Sales]),-1))
>
WINDOW_AVG(
(
SUM([Sales])
-
LOOKUP(SUM([Sales]),-1)
)
/
ABS(LOOKUP(SUM([Sales]),-1))
)
This highlights categories outperforming the business average growth rate.
