Free PowerPivot exercises
You are welcome to try any of the 72 PowerPivot exercises listed below, but please do not distribute them in any form without asking for our written permission first.
| Software: |
PowerPivot |
| Topic: |
PowerPivot data models |
| Level: |
Relatively easy |
| Exercise: |
From the MAM database, import 6 tables and use them to show quantity sold by town. |
| Software: |
PowerPivot |
| Topic: |
PowerPivot data models |
| Level: |
Relatively easy |
| Exercise: |
Import tables from the Make-a-Mammal database, then hide tables and columns to create a clean data model. |
| Software: |
PowerPivot |
| Topic: |
PowerPivot data models |
| Level: |
Average difficulty |
| Exercise: |
Import tables into PowerPivot, hide tables and columns and create a pivot table and slicer. |
| Software: |
PowerPivot |
| Topic: |
PowerPivot data models |
| Level: |
Harder than average |
| Exercise: |
Create two pivot tables, and two timelines which control both of the pivot tables. |
| Software: |
PowerPivot |
| Topic: |
Using Excel tables |
| Level: |
Average difficulty |
| Exercise: |
Create a linked Excel workbook in PowerPivot and use it in relationships. |
| Software: |
PowerPivot |
| Topic: |
Using Excel tables |
| Level: |
Average difficulty |
| Exercise: |
Import data from Access, Word and Excel, and link an Excel table, to create a PowerPivot data model. |
| Software: |
PowerPivot |
| Topic: |
Using Excel tables |
| Level: |
Average difficulty |
| Exercise: |
Link to Excel, Access and the clipboard (via Word) to import and link 4 tables. |
| Software: |
PowerPivot |
| Topic: |
Calculated columns |
| Level: |
Relatively easy |
| Exercise: |
Create an aggregator column to sum transaction values by weekday in a pivot table. |
| Software: |
PowerPivot |
| Topic: |
Calculated columns |
| Level: |
Relatively easy |
| Exercise: |
Create two new calculated columns in a table, using RELATED and CONCATENATE. |
| Software: |
PowerPivot |
| Topic: |
Calculated columns |
| Level: |
Relatively easy |
| Exercise: |
Total sales by weekday, using simple two calculated columns. |
| Software: |
PowerPivot |
| Topic: |
Calculated columns |
| Level: |
Average difficulty |
| Exercise: |
Calculate age bands for different dates using the SWITCH functon. |
| Software: |
PowerPivot |
| Topic: |
Calculated columns |
| Level: |
Average difficulty |
| Exercise: |
Divide shopping centres into the circles of hell, using the IF and the SWITCH functions. |
| Software: |
PowerPivot |
| Topic: |
Calculated columns |
| Level: |
Average difficulty |
| Exercise: |
Divide years into bands using SWITCH and calculated columns. |
| Software: |
PowerPivot |
| Topic: |
Calculated columns |
| Level: |
Harder than average |
| Exercise: |
Summarise sales by status of animal, using calculated columns. |
| Software: |
PowerPivot |
| Topic: |
Measures |
| Level: |
Average difficulty |
| Exercise: |
Calculate total and average transaction values using measures. |
| Software: |
PowerPivot |
| Topic: |
Measures |
| Level: |
Harder than average |
| Exercise: |
Calculate ratio of area to units for shopping centres using AVERAGEX. |
| Software: |
PowerPivot |
| Topic: |
The CALCULATE function |
| Level: |
Relatively easy |
| Exercise: |
Use the CALCULATE function to pick out only transactions whose price is a given amount. |
| Software: |
PowerPivot |
| Topic: |
The CALCULATE function |
| Level: |
Relatively easy |
| Exercise: |
Use the CALCULATE function to show percentages of row and column totals in a pivot table. |
| Software: |
PowerPivot |
| Topic: |
The CALCULATE function |
| Level: |
Average difficulty |
| Exercise: |
Create a ratio of sales between two different habitats, using the CALCULATE and SUMX functions. |
| Software: |
PowerPivot |
| Topic: |
The CALCULATE function |
| Level: |
Average difficulty |
| Exercise: |
Use CALCULATE to work out the ratio of total sales to sales for a specific type of animal. |
| Software: |
PowerPivot |
| Topic: |
The CALCULATE function |
| Level: |
Average difficulty |
| Exercise: |
Use the CALCULATE function to show total sales for Northern powerhouse shopping centres. |
| Software: |
PowerPivot |
| Topic: |
The CALCULATE function |
| Level: |
Harder than average |
| Exercise: |
Omitting a particular shopping centre from totals, using the VALUES function. |
| Software: |
PowerPivot |
| Topic: |
The CALCULATE function |
| Level: |
Harder than average |
| Exercise: |
Use AVERAGEX to find average ratios, then CALCULATE to avoid divide-by-zero errors. |
| Software: |
PowerPivot |
| Topic: |
More advanced DAX functions |
| Level: |
Average difficulty |
| Exercise: |
Exclude a single animal from a pivot table, using CALCULATE combined with the VALUES function. |
| Software: |
PowerPivot |
| Topic: |
Calendars |
| Level: |
Average difficulty |
| Exercise: |
Create and import a calendar in Excel or SQL giving star signs. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Relatively easy |
| Exercise: |
Use the SAMEPERIODLASTYEAR and DATEADD functions to show various statistics for total sales. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Relatively easy |
| Exercise: |
Use the TOTALQTD and TOTALYTD functions to show total sales with quarterly and annual to-date figures. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Relatively easy |
| Exercise: |
Use the TOTALYTD and TOTALQTD functions to show cumulative totals. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Average difficulty |
| Exercise: |
Calculate total sales for the last year and the one before it. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Average difficulty |
| Exercise: |
Use FIRSTDATE and LASTDATE to pick out the start/end values. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Average difficulty |
| Exercise: |
Use the SAMEPERIODLASTYEAR and DATEADD functions to show comparison figures. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Average difficulty |
| Exercise: |
Use the TOTALQTD function to accumulate quarter-to-date figures for each environment. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Harder than average |
| Exercise: |
Compare YTD totals for two years, showing variance. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Harder than average |
| Exercise: |
Use the DATESYTD, TOTALYTD, SAMEPERIODLASTYEAR and various other date functions to compare totals. |
| Software: |
PowerPivot |
| Topic: |
Date functions |
| Level: |
Harder than average |
| Exercise: |
Use the PARALLELPERIOD function to show this quarter's total sales divided by the previous quarter's. |
| Software: |
PowerPivot |
| Topic: |
Hierarchies |
| Level: |
Not specified |
| Exercise: |
Create a pivot table using two hierarchies. |
| Software: |
PowerPivot |
| Topic: |
Hierarchies |
| Level: |
Average difficulty |
| Exercise: |
Create hierarchies using the RELATED function to make a pivot table look neat and tidy. |
| Software: |
PowerPivot |
| Topic: |
KPIs |
| Level: |
Relatively easy |
| Exercise: |
Create a KPI giving a 10% target proportion of total sales. |
| Software: |
PowerPivot |
| Topic: |
KPIs |
| Level: |
Relatively easy |
| Exercise: |
Use a KPI to show how well you are performing against the previous quarter. |
| Software: |
PowerPivot |
| Topic: |
KPIs |
| Level: |
Average difficulty |
| Exercise: |
Create a KPI, showing green if a period's sales are more than 10% of the same period two months previously. |
| Software: |
PowerPivot |
| Topic: |
KPIs |
| Level: |
Average difficulty |
| Exercise: |
Create an absolute KPI that a product should contribute 10% of total sales. |
| Software: |
PowerPivot |
| Topic: |
KPIs |
| Level: |
Average difficulty |
| Exercise: |
Set a KPI that sales should be equal to previous year's. |
| Software: |
PowerPivot |
| Topic: |
Power View |
| Level: |
Not specified |
| Exercise: |
Use Power Map to create and play a tour showing varying sales quantities by town over time. |
| Software: |
PowerPivot |
| Topic: |
Power View |
| Level: |
Relatively easy |
| Exercise: |
Create a tiled report in Power View, containing one table and one chart. |
| Software: |
PowerPivot |
| Topic: |
Power View |
| Level: |
Average difficulty |
| Exercise: |
Use maps in Power View to create multiple pie charts with drill down. |
| Software: |
PowerPivot |
| Topic: |
Power View |
| Level: |
Average difficulty |
| Exercise: |
Use Power View to show a report of pie charts by month and animal, with filtering applied. |
| Software: |
PowerPivot |
| Topic: |
Power BI Desktop overview |
| Level: |
Relatively easy |
| Exercise: |
In Power BI Desktop, import some data and create a table visualisation with some formatting. |
| Software: |
PowerPivot |
| Topic: |
Power BI Desktop overview |
| Level: |
Average difficulty |
| Exercise: |
Create and format a slicer, and also a column chart with formatting, and use them to show sales for 2 centre types. |
| Software: |
PowerPivot |
| Topic: |
Power BI Desktop overview |
| Level: |
Harder than average |
| Exercise: |
Create a matrix with conditional formatting and a tree diagram on two separate report pages. |
| Software: |
PowerPivot |
| Topic: |
Power BI Desktop maps |
| Level: |
Average difficulty |
| Exercise: |
Create a map showing a pie slices of purchases by family for each town in the South-West of the UK. |
You can search our full list of Power BI exercises here.
Or why not learn to do them the right way on one of our Power BI training courses? You can attend our online training classes wherever you are in the world!
