1. How would you identify customers who generated profit in every year?
Count the number of years with positive profit and compare it with total years.
{ FIXED [Customer ID] :
COUNTD(
IF [Profit] > 0
THEN YEAR([Order Date])
END
)
}
=
{ FIXED : COUNTD(YEAR([Order Date])) }
This returns customers who were profitable every year.
2. How would you calculate the percentage change between current sales and average sales?
Compare current sales against overall average sales.
(
SUM([Sales])
-
WINDOW_AVG(SUM([Sales]))
)
/
WINDOW_AVG(SUM([Sales]))
This shows how much a value is above or below the average.
3. How would you identify the customer with the highest single order value?
Compare each order against the maximum order amount.
SUM([Sales])
=
WINDOW_MAX(SUM([Sales]))
This returns the order/customer with the highest order value.
4. How would you calculate the ratio of profit to quantity sold?
Measure profit earned per unit sold.
SUM([Profit])
/
SUM([Quantity])
Useful for analyzing product efficiency and profitability.
5. How would you identify products whose sales are greater than the average sales of their category?
Compare product sales with category-level average using FIXED LOD.
SUM([Sales])
>
{ FIXED [Category] : AVG([Sales]) }
This highlights products outperforming their category average.
