Power Pivot and DAX: practice exercises
Move beyond a single table and work with a data model. These exercises introduce relationships between tables, the star schema and DAX measures, from a simple SUM to CALCULATE, filter context and time intelligence such as year to date. Measures are evaluated in the browser, so you see the value change as soon as the formula is right. A useful next step after PivotTables if your reports pull data from several sources.
28 exercises · level: Analyst
Start with the first task Study the course: Power Pivot and DAX →
Showing 28 of 28
Model and Measures
Add up the sales amounts. Name the measure "Revenue".
Count how many units were sold. Name the measure "Units Sold".
Count the sales rows. Name the measure "Transactions".
Divide revenue by the number of transactions with DIVIDE. Name the measure "Average Sale".
Count how many different products were sold. Name the measure "Distinct Products".
Calculate revenue for the "Beverages" category only. Name the measure "Beverage Revenue".
Calculate the revenue of the Chicago stores. Name the measure "Chicago Revenue".
Calculate revenue only from sales of $40 or more, using FILTER over the Sales table. Name the measure "Large Sales".
Create a "Transactions" measure (COUNTROWS), then count the sales of 5 or more units. Name the measure "Bulk Sales".
Calculate revenue that ignores any filter on products and categories. Name the measure "All Products Revenue".
Calculate the row's share of the revenue of all products. Name the measure "Product Share".
Calculate the revenue of the whole chain, ignoring every filter. Name the measure "Total Revenue".
Calculate the row's share of the revenue of the whole chain. Name the measure "Share of Total".
Calculate the cost of what was sold: quantity × unit cost from the lookup table. Name the measure "Cost".
Calculate profit: revenue minus cost. Name the measure "Profit".
Calculate the margin: profit / revenue. Name the measure "Margin".
Calculate what the sales would be worth at the list price from the lookup table, with no discounts. Name the measure "List Price Revenue".
Calculate the total discounts: list price revenue minus actual revenue. Name the measure "Discounts".
Time and Model Quality
Calculate revenue as a running total from the start of the year. Name the measure "YTD Revenue".
Calculate revenue for the same period of the previous year. Name the measure "Prior Year".
Calculate revenue growth over the previous year as a fraction (0.1 = 10%). Name the measure "YoY Growth".
Calculate the previous month's revenue with DATEADD. Name the measure "Prior Month".
Project
The sum of the sales. Name the measure "Revenue".
Create the "Cost" and "Profit" measures, and from them the margin (profit / revenue). Name the measure "Margin".
The row's share of the revenue of all products (create a helper measure, or write CALCULATE right inside DIVIDE). Name the measure "Product Share".
Growth over the same period of the previous year. Name the measure "YoY Growth".
The average revenue per store. Name the measure "Average Store Revenue".
The revenue of the most successful month in the current filter. Name the measure "Best Month".
No exercises match these filters.