29. Practice Exercises and Final Assignment
29.7 Module 13: DAX (with answer hints)
| # | Exercise | Hint (one possible answer) |
|---|---|---|
| 1 | Number of unique products sold | DISTINCTCOUNT(Orders[Product ID]) |
| 2 | Average items (quantity) per order | AVERAGEX(VALUES(Orders[Order ID]), [Total Quantity]) |
| 3 | Sales of the Fruits & Vegetables category only | CALCULATE([Total Sales], Product[Category] = "Fruits & Vegetables") |
| 4 | Orders in Nagpur or Sambhaji Nagar paid by UPI | CALCULATE([Total Orders], DarkStore[City] IN {"Nagpur","Sambhaji Nagar"}, Orders[Payment Mode] = "UPI") |
| 5 | Gross sales before discount without a calculated column | SUMX(Orders, Orders[Amount] + Orders[Discount]) |
| 6 | Discount as % of gross sales | DIVIDE([Total Discount], [Total Sales] + [Total Discount]) |
| 7 | Each city's % share of total orders | DIVIDE([Total Orders], CALCULATE([Total Orders], ALL(DarkStore))) |
| 8 | Each product's % of sales within its category | DIVIDE([Total Sales], CALCULATE([Total Sales], ALLEXCEPT(Product, Product[Category]))) |
| 9 | Calculated column in Orders: store city | RELATED(DarkStore[City]) |
| 10 | Calculated column in Customer: number of orders | CALCULATE(DISTINCTCOUNT(Orders[Order ID])) – context transition |
| 11 | Calculated column in Customer: "Frequent" if more than 20 orders, "Regular" if more than 5, else "Occasional" | SWITCH(TRUE(), [Total Orders] > 20, "Frequent", [Total Orders] > 5, "Regular", "Occasional") (measure reference in a column triggers context transition) |
| 12 | Cancellation rate for Cash on Delivery orders | CALCULATE([Cancellation Rate], Orders[Payment Mode] = "Cash on Delivery") |
| 13 | % of orders delivered in more than 20 minutes | DIVIDE(CALCULATE([Delivered Orders], Orders[Delivery Time Mins] > 20), [Delivered Orders]) |
| 14 | Average delivery time for Blinkit only | CALCULATE([Avg Delivery Time (mins)], Orders[Platform] = "Blinkit") |
| 15 | Difference in AOV: Blinkit minus Amazon Now | CALCULATE([AOV], Orders[Platform] = "Blinkit") - CALCULATE([AOV], Orders[Platform] = "Amazon Now") |
| 16 | Orders Month-to-Date | TOTALMTD([Total Orders], 'Date'[Date]) |
| 17 | AOV same period last year and AOV YoY % | VAR ly = CALCULATE([AOV], SAMEPERIODLASTYEAR('Date'[Date])) RETURN DIVIDE([AOV] - ly, ly) |
| 18 | Orders counted by delivery date | CALCULATE([Total Orders], USERELATIONSHIP(Orders[Delivered Date], 'Date'[Date])) |
| 19 | Running total of sales | CALCULATE([Total Sales], FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]))) |
| 20 | Rank dark stores by orders (dense) | RANKX(ALL(DarkStore[Store Name]), [Total Orders], , DESC, DENSE) |
| 21 | Sales of the top 10 customers | CALCULATE([Total Sales], TOPN(10, ALL(Customer[Customer Name]), [Total Sales])) |
| 22 | Show the selected platform or "All Platforms" | SELECTEDVALUE(Orders[Platform], "All Platforms") |
| 23 | Orders delivered by partners riding EV scooters | CALCULATE([Delivered Orders], DeliveryPartner[Vehicle Type] = "EV Scooter") |
| 24 | Peak order hour (hour with the most orders) | Hint: MAXX(TOPN(1, VALUES(Orders[Order Hour]), [Total Orders]), Orders[Order Hour]) |
| 25 | Sales of Nashik Grapes and Nagpur Oranges together | CALCULATE([Total Sales], Product[Product Name] IN {"Nashik Grapes 500 g", "Nagpur Oranges 1 kg"}) |
| 26 | Orders on festival days (Diwali, Ganeshotsav) | CALCULATE([Total Orders], 'Date'[Is Festival Day] = TRUE()) |
| 27 | Kolhapur's share of Maharashtra orders | DIVIDE(CALCULATE([Total Orders], DarkStore[City] = "Kolhapur"), CALCULATE([Total Orders], ALL(DarkStore))) |
| 28 | New customers this period (first order in the period) | Hint: for each customer compare CALCULATE(MIN(Orders[Order Date]), ALL('Date')) with the dates visible in the current period, and count with FILTER |
Ravindra Bagale's Tip
Mitrano, khup students copy the hint formula without understanding it. Type the DAX yourself, then change one part (udaharan mhanje the filter or the time period) and predict the result before you look. Ha niyam lakshat theva.