Appendix A: Answers to Practice Exercises
This appendix provides the answers to the practice exercises scattered throughout the book. The answers are in the same order the exercises appear in the book and are numbered so you can easily match up the exercises and the answers.
SUM() These practice exercises appear in Chapter 4. As you compare your answers to the ones shown here, consider the following questions: Did you remember to put your measures in the correct table? Did you put the measure in the table where the data comes from? Did you format with an appropriate number format?
1. Total Sales
= SUM(Sales[ExtendedAmount])
Or:
Total Sales
= SUM(Sales[SalesAmount])
2. Total Cost
= SUM(Sales[TotalProductCost])
Or:
Total Cost
= SUM(Sales[ProductStandardCost])
3. Total Margin $
= [Total Sales] – [Total Cost]
4. Total Margin % = [Total Margin $] / [Total Sales]
Or:
Total Margin % = DIVIDE([Total Margin $] , [Total Sales])
5. Total Sales Tax Paid
= SUM(Sales[TaxAmt])
6. Total Sales Including Tax
= [Total Sales] + [Total Sales Tax Paid]
7. Total Order Quantity
= SUM(Sales[OrderQuantity])
COUNT() These practice exercises appear in Chapter 4.
8. Total Number of Products
= COUNT(Products[ProductKey])
9. Total Number of Customers
= COUNT(Customers[CustomerKey])
Note: Counting the “key” columns is generally pretty safe because, by definition, each one must have a value. Technically, you can count any column that has a numeric value in each cell, and you will get the same answer. Just be careful if you are counting a numeric column that may have blank values because COUNT()will not count blanks.
COUNTROWS() These practice exercises appear in Chapter 4.
Note: Remember that COUNTROWS() takes a table, not a column, as input.
10. Total Number of Products COUNTROWS Version
= COUNTROWS(Products)
11. Total Number of Customers COUNTROWS Version
= COUNTROWS(Customers)
DISTINCTCOUNT() These practice exercises appear in Chapter 4.
12. Total Customers in Database DISTINCTCOUNT Version
= DISTINCTCOUNT(Customers[CustomerKey])
13. Count of Occupation
= DISTINCTCOUNT(Customers[Occupation])
14. Count of Country
= DISTINCTCOUNT(Territories[Country])
15. Total Customers That Have Purchased
= DISTINCTCOUNT(Sales[CustomerKey])
MAX(), MIN(), and AVERAGE() These practice exercises appear in Chapter 4.
16. Maximum Tax Paid on a Product
= MAX(Sales[TaxAmt])
17. Minimum Price Paid for a Product
= MIN(Sales[ExtendedAmount])
18. Average Price Paid for a Product
= AVERAGE(Sales[ExtendedAmount])
COUNTBLANK() These practice exercises appear in Chapter 4.
19. Customers Without Address Line 2
= COUNTBLANK(Customers[AddressLine2])
20. Products Without Weight Values
= COUNTBLANK(Products[Weight])
DIVIDE() These practice exercises appear in Chapter 4.
21. Margin %
= DIVIDE([Total Margin $] , [Total Sales])
22. Markup %
= DIVIDE([Total Margin $] , [Total Cost])
23. Tax %
= DIVIDE([Total Sales Tax Paid] , [Total Sales])
SUMX() These practice exercises appear in Chapter 7.
24. Total Sales SUMX Version
= SUMX(Sales, Sales[OrderQuantity] * Sales[UnitPrice])
Note: In this sample database, the order quantity is always 1.
25. Total Sales Including Tax SUMX Version
= SUMX(Sales,Sales[ExtendedAmount] + Sales[TaxAmt])
26. Total Sales Including Freight
= SUMX(Sales,Sales[ExtendedAmount] + Sales[Freight])
27. Dealer Margin
= SUMX (Products,Products[ListPrice] - Products[DealerPrice])
AVERAGEX() These practice exercises appear in Chapter 7.
28. Average Sell Price per Item
= AVERAGEX(Sales, Sales[UnitPrice])
Note: The expression can be a single column. It doesn’t have to be an equation using multiple columns.
Or: Average Sell Price per Item Weighted
= AVERAGEX(Sales, Sales[OrderQuantity] * Sales[UnitPrice])
In fact, this sample database always has Sales[OrderQuantity] = 1, so the answer will be the same as the previous formula.
29. Average Tax Paid = AVERAGEX(Sales, Sales[TaxAmt])
30. Average Safety Stock
= AVERAGEX(Products, Products[SafetyStockLevel])
Calculated Columns This practice exercise appears in Chapter 8.
31. = IF(
OR('Calendar'[CalendarQuarter]=1,
'Calendar'[CalendarQuarter]=2
),
"H1","H2"
)
Note: There are a number of ways to write this calculated column. If yours is different from this but works, then all is well and good.
CALCULATE() with a Single Table These practice exercises appear in Chapter 9.
32. Total Male Customers
= CALCULATE([Total Number of Customers],
Customers[Gender] = "M")
33. Total Customers Born Before 1950
= CALCULATE([Total Number of Customers],
Customers[BirthDate] < DATE(1950,1,1))
34. Total Customers Born in January
= CALCULATE([Total Number of Customers],
MONTH(Customers[BirthDate])=1)
35. Customers Earning at Least $100,000 per Year
= CALCULATE([Total Number of Customers],
Customers[YearlyIncome]>=100000)
CALCULATE() with Multiple Tables These practice exercises appear in Chapter 9.
36. Total Sales of Clothing
= CALCULATE([Total Sales],
Products[Category]="Clothing")
37. Sales to Female Customers
= CALCULATE([Total Sales],
Customers[Gender]="F")
38. Sales of Bikes to Married Men
= CALCULATE([Total Sales],
Customers[MaritalStatus]="M",
Customers[Gender]="M",
Products[Category]="Bikes"
)
VALUES() These practice exercises appear in Chapter 12.
39. Number of Color Variants
= COUNTROWS(VALUES(Products[Color]))
40. Number of Sub Categories
= COUNTROWS(VALUES(Products[SubCategory]))
41. Number of Size Ranges
= COUNTROWS(VALUES(Products[SizeRange]))
42. Product Category (Values)
= IF(HASONEVALUE(Products[Category]),
VALUES(Products[Category])
)
Or:
= SELECTEDVALUE(Products[Category])
43. Product Subcategory (Values)
= IF(HASONEVALUE(Products[SubCategory]),
VALUES(Products[SubCategory])
)
Or: = SELECTEDVALUE(Products[SubCategory])
44. Product Color (Values)
= IF(HASONEVALUE(Products[color]),
VALUES(Products[color])
)
Or: = SELECTEDVALUE(Products[color])
45. Product Subcategory (Values) edited
= IF(HASONEVALUE(Products[SubCategory]),
VALUES(Products[SubCategory]),
"More than 1 Sub Cat"
)
Or: = SELECTEDVALUE(Products[SubCategory]),"More than 1 Sub Cat")
46. Product Color (Values) edited
= IF(HASONEVALUE(Products[color]),
VALUES(Products[color]),
"More than 1 Color"
)
Or: = SELECTEDVALUE(VALUES(Products[color]),"More than 1 Color")
ALL(), ALLEXCEPT(), and ALLSELECTED() These practice exercises appear in Chapter 13.
47. Total Sales to All Customers
= CALCULATE([Total Sales] , All(Customers))
Note: This measure belongs in the Sales table, not the Customers table.
48. % of All Customer Sales
= DIVIDE([Total Sales] , [Total Sales to All Customers])
49. Total Sales to Selected Customers
= CALCULATE([Total Sales] , ALLSELECTED(Customers))
50. % of Sales to Selected Customers
= DIVIDE([Total Sales] , [Total Sales to Selected Customers])
51. Total Sales for All Days Selected Dates
= CALCULATE([Total Sales] , ALLSELECTED('Calendar'))
Note: Did you know to use ALLSELECTED() and not ALLEXCEPT()?
52. % Sales for All Days Selected Dates
= DIVIDE([Total Sales] ,[Total Sales for All Days Selected Dates])
53. Total Orders All Customers
= CALCULATE([Total Order Quantity] , ALL(Customers))
54. Baseline Orders for All Customers with This Occupation
= CALCULATE([Total Order Quantity] ,
ALLEXCEPT(Customers, Customers[Occupation])
)
55. Baseline % This Occupation of All Customer Orders
= DIVIDE(
[Baseline Orders for All customers with this Occupation] ,
[Total Orders All Customers]
)
56. Total Orders Selected Customers
= CALCULATE([Total Order Quantity] , ALLSELECTED(Customers])
57. Occupation % of Selected Customers
= DIVIDE(
[Total Order Quantity] ,
[Total Orders Selected Customers]
)
58. Percentage Point Variation to Baseline
= [Occupation % of Selected Customers] -
[Baseline % this Occupation is of All Customer Orders]
FILTER() These practice exercises appear in Chapter 14.
59. Total Sales of Products That Have Some Sales but Less Than $10,000
= CALCULATE([Total Sales],
FILTER(Products,
[Total Sales] < 10000 &&
[Total Sales] >0
)
)
Or: = CALCULATE([Total Sales],
FILTER(Products, [Total Sales] <10000),
FILTER(Products, [Total Sales] >0)
)
60. Count of Products That Have Some Sales but Less Than $10,000
= CALCULATE(COUNTROWS(Products),
FILTER(Products,
[Total Sales]<10000 && [Total Sales] >0
)
)
Or: = CALCULATE(COUNTROWS(Products),
FILTER(Products, [Total Sales] <10000),
FILTER(Products, [Total Sales] >0)
)
Time Intelligence These practice exercises appear in Chapter 15.
61. Total Sales Month to Date
= TOTALMTD([Total Sales], 'Calendar'[Date])
62. Total Sales Quarter to Date
= TOTALQTD([Total Sales], 'Calendar'[Date])
63. Total Sales FYTD 30 June
= TOTALYTD([Total Sales],'Calendar'[Date],"30/6")
64. Total Sales FYTD 31 March
= TOTALYTD([Total Sales],'Calendar'[Date],"31/3")
65. Total Sales Previous Month
= CALCULATE([Total Sales],
PREVIOUSMONTH(Calendar[Date])
)
66. Total Sales Previous Day
= CALCULATE([Total Sales],
PREVIOUSDAY('Calendar'[Date])
)
67. Total Sales Previous Quarter
= CALCULATE([Total Sales],
PREVIOUSQUARTER('Calendar'[Date])
)
68. Total Sales Moving Annual Total
= CALCULATE([Total Sales],
FILTER(ALL('Calendar'),
'Calendar'[ID] > MAX('Calendar'[ID]) - 365 &&
'Calendar'[ID] <= MAX('Calendar'[ID])
)
)
69. Total Sales Rolling 90 Days
= IF(MAX('Calendar'[ID])>=90,
CALCULATE([Total Sales],
FILTER(ALL('Calendar'),
'Calendar'[ID] > MAX('Calendar'[ID]) - 90 &&
'Calendar'[ID] <= MAX('Calendar'[ID])
)
)
)
Harvester Measures This practice exercise appears in Chapter 17.
70. Total Customers Born Before Selected Year
= CALCULATE ( [Total number of Customers],
FILTER (Customers,
Customers[BirthDate] < DATE ( [Selected Year], 1, 1 )
)
)
Multiple Data Tables These practice exercises appear in Chapter 18.
71. Total Budget = SUM(Budget[Budget])
This measure should be placed in the Budget table.
72. Change in Sales vs. Budget
= [Total Sales] – [Total Budget]
This measure could be placed in either the Sales table or the Budget table. I normally place it in the Sales table because the name of the measure is [Change in Sales vs. Budget].
73. % Change in Sales vs. Budget
= DIVIDE([Change in Sales vs. Budget] , [Total Budget])
Also place this measure in the Sales table.