1. How would you identify customers whose sales doubled compared to the previous year?
Compare current year sales with previous year sales and flag customers with >100% growth.
(
SUM([Sales])
-
LOOKUP(SUM([Sales]),-1)
)
/
ABS(LOOKUP(SUM([Sales]),-1))
>= 1
This identifies customers whose sales increased by 100% or more.
2. How would you calculate the percentage of products contributing to 80% of sales (Pareto Analysis)?
Calculate cumulative sales percentage and identify products within the 80% threshold.
RUNNING_SUM(SUM([Sales]))
/
TOTAL(SUM([Sales]))
<= 0.8
Sort products by Sales descending before applying the calculation.
3. How would you identify customers who purchased from all product categories?
Count distinct categories purchased by a customer and compare with total categories.
{ FIXED [Customer ID] :
COUNTD([Category])
}
=
{ FIXED :
COUNTD([Category])
}
This finds customers with purchases across every category.
4. How would you calculate average profit per active month for each product?
Divide total product profit by the number of months in which the product was sold.
{ FIXED [Product Name] :
SUM([Profit])
}
/
{ FIXED [Product Name] :
COUNTD(
DATETRUNC('month',[Order Date])
)
}
This helps compare products fairly regardless of sales duration.
5. How would you identify orders where sales are above the average order sales?
Compare each order's sales against the average order sales.
SUM([Sales])
>
WINDOW_AVG(SUM([Sales]))
Compute using Order ID.
This highlights unusually large orders.
6. How would you calculate the percentage of customers who are repeat customers?
Divide customers with more than one order by total customers.
COUNTD(
IF
{ FIXED [Customer ID] :
COUNTD([Order ID])
} > 1
THEN [Customer ID]
END
)
/
COUNTD([Customer ID])
This is a common customer loyalty metric.
