Ravindra BagaleCourses & study guides मराठी Track your progress

29. Practice Exercises and Final Assignment

29.7 Module 13: DAX (with answer hints)

By Last updated:

# 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

About this data: This table uses columns: Exercise, Hint (one possible answer). Values shown are the lesson examples already on this page (not a new dataset).

Practice files (fictional): visuals_orders_apr_may_2026.csv, column_vs_measure_orders.csv

Hints
  • Load the practice file, write the measure, and compare with the value before opening the solution.
  • Exercises that need Product, Customer or DeliveryPartner tables use the full course dataset (Module 1.7); check them with a query-view total (section 13.27).
Check on the small practice file (selected exercises)
  1. Exercise 3: Category = Fruits: ₹400
  2. Exercise 5: Gross before discount (column_vs_measure file): 1,900 + 150 = ₹2,050
  3. Exercise 6: 150 ÷ 2,050 = 7.3%
  4. Exercise 7: Pune 37.5%, Nashik 37.5%, Kolhapur 25.0% (orders 3/3/2 of 8)
  5. Exercise 12: Overall cancellation 1 ÷ 8 = 12.5%
  6. Exercise 13: 0% – no delivery above 20 min
  7. Exercise 14: Blinkit (301, 303, 304, 306): 47 ÷ 4 = 11.75 min
  8. Exercise 15: Blinkit AOV ₹275 − Amazon Now ₹333.33 = −₹58.33
  9. Exercise 16: At 05-May-2026: 5 May orders
  10. Exercise 19: By month: Apr ₹900, May ₹2,100
  11. Exercise 20: Stores by orders (2, 2, 2, 1, 1): dense ranks 1, 1, 1, 2, 2
  12. Exercise 22: No selection → "All Platforms"
  13. Exercise 27: Kolhapur 2 ÷ 8 = 25.0%

Ravindra Bagale's Tip

Friends, many students copy the hint formula without understanding it. Type the DAX yourself, then change one part (for example the filter or the time period) and predict the result before you look. Remember this rule.

Loading DAX practice tasks...

  1. Total Premium (Easy): Measure: Premium Sum = SUM of Premium.
  2. Rows per Agent (Easy): Shown per Agents Name. Measure: Row Count = number of Policies rows.
  3. Total ClaimPaid per Line (Easy): Shown per Line. Measure: Total ClaimPaid = SUM of ClaimPaid.
  4. Loss Ratio (Medium): Shown per Agents Name. Measure: Loss Ratio = SUM of ClaimPaid ÷ SUM of Premium.
  5. Premium for Health only (Medium): Shown per City. Measure: Premium Health = SUM of Premium where Line is "Health".
  6. Loss Ratio against target (Hard): Shown per Agents Name. Measure: Loss Ratio Status = "On target" when Loss Ratio is <= 0.6, else "Check".
  7. Loss Ratio for Health (Hard): Shown per Country. Measure: Loss Ratio Health = Loss Ratio where Line is "Health".
  8. Share of Premium (Medium): Shown per Agents Name. Measure: Premium Share = SUM of Premium ÷ SUM of Premium for all of Agents.
  9. Premium minus ClaimPaid (Hard): Shown per Line. Measure: Gap = SUMX over Policies of Premium − ClaimPaid.
  10. Rank by Premium (Hard): Shown per Agents Name. Measure: Premium Rank = rank of [Total Premium] among all Agents Name values.
  11. claims band (Medium): Calculated column on Policies: Claims Band = "High" when Claims is more than 1, else "Normal".

See all Power BI practice tasks