WISE OWL EXERCISES

POWER BI EXERCISES

POWERPIVOT EXERCISES

- PowerPivot data models (7)
- Pivot tables using PowerPivot (2)
- Using Excel tables (3)
- Using other data sources (1)
- Transforming data (Power Query) (7)
- Calculated columns (7)
- Measures (2)
- The CALCULATE function (15)
- More advanced DAX functions (5)
- Calendars (1)
- Date functions (10)
- Hierarchies (2)
- KPIs (5)
- Power View (4)
- Power BI Desktop overview (3)
- Power BI Desktop maps (1)

# Free PowerPivot exercises

This page lists 75 **PowerPivot** exercises. You can use the menu to show just exercises for a specific topic.

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Power View |

Level: | |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use Power Map to create and play a tour showing varying sales quantities by town over time. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Hierarchies |

Level: | |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a pivot table using two hierarchies. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Calculated columns |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create an aggregator column to sum transaction values by weekday in a pivot table. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Calculated columns |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Total sales by weekday, using simple two calculated columns. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Transforming data (Power Query) |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | For a table of populations, change data types and make other changes in Power Query. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | PowerPivot data models |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | From the MAM database, import 6 tables and use them to show quantity sold by town. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | PowerPivot data models |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a pivot table of average prices for transactions. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Power View |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a tiled report in Power View, containing one table and one chart. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | KPIs |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use a KPI to show how well you are performing against the previous quarter. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Power BI Desktop overview |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | In Power BI Desktop, import some data and create a table visualisation with some formatting. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | PowerPivot data models |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Import tables from the Make-a-Mammal database, then hide tables and columns to create a clean data model. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Date functions |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the TOTALYTD and TOTALQTD functions to show cumulative totals. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Transforming data (Power Query) |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Import a population table using Power Query, then tidy up the data. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | KPIs |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a KPI giving a 10% target proportion of total sales. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | The CALCULATE function |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the CALCULATE function to show percentages of row and column totals in a pivot table. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Date functions |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the SAMEPERIODLASTYEAR and DATEADD functions to show various statistics for total sales. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Date functions |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the TOTALQTD and TOTALYTD functions to show total sales with quarterly and annual to-date figures. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | The CALCULATE function |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the CALCULATE function to pick out only transactions whose price is a given amount. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | The CALCULATE function |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use ALL and CALCULATE to get proportions of totals for regions. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | The CALCULATE function |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Divide sales by 4 legs and other for a measure, using the FILTER function. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Calculated columns |

Level: | Relatively easy |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create two new calculated columns in a table, using RELATED and CONCATENATE. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Power BI Desktop overview |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

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 |

Version: | Excel 2016 and later |

Topic: | PowerPivot data models |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Import tables into PowerPivot, hide tables and columns and create a pivot table and slicer. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Hierarchies |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create hierarchies using the RELATED function to make a pivot table look neat and tidy. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | KPIs |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a KPI, showing green if a period's sales are more than 10% of the same period two months previously. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Power View |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use maps in Power View to create multiple pie charts with drill down. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Power BI Desktop maps |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a map showing a pie slices of purchases by family for each town in the South-West of the UK. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Power View |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use Power View to show a report of pie charts by month and animal, with filtering applied. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Pivot tables using PowerPivot |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use slicers to control 2 pivot tables, and Quick Explore to drill down. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Calendars |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create and import a calendar in Excel or SQL giving star signs. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | The CALCULATE function |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the CALCULATE function to show total sales for Northern powerhouse shopping centres. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Measures |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Calculate total and average transaction values using measures. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Transforming data (Power Query) |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | How to unpivot data in Power Query, then use this as a basis for a pivot table. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Using Excel tables |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Link to Excel, Access and the clipboard (via Word) to import and link 4 tables. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Transforming data (Power Query) |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Tidy up exchange rate and investment data in Power Query, splitting columns and replacing values. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Using Excel tables |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Import data from Access, Word and Excel, and link an Excel table, to create a PowerPivot data model. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | PowerPivot data models |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Import a table then amend it and import others to create data model. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | KPIs |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Set a KPI that sales should be equal to previous year's. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Using Excel tables |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a linked Excel workbook in PowerPivot and use it in relationships. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | The CALCULATE function |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the RANKX function to order total sales over species. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | KPIs |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create an absolute KPI that a product should contribute 10% of total sales. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Date functions |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the TOTALQTD function to accumulate quarter-to-date figures for each environment. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | The CALCULATE function |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use FILTER and ALL to show sales as a proportion of one region's total. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | The CALCULATE function |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create measure using CALCULATE and ALL to get ratios against total. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Date functions |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use FIRSTDATE and LASTDATE to pick out the start/end values. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Date functions |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the SAMEPERIODLASTYEAR and DATEADD functions to show comparison figures. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Transforming data (Power Query) |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Divide exchange rate and investment symbols into more parts by splitting and removing columns. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | The CALCULATE function |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a ratio of sales between two different habitats, using the CALCULATE and SUMX functions. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Calculated columns |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Divide shopping centres into the circles of hell, using the IF and the SWITCH functions. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Date functions |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Calculate total sales for the last year and the one before it. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | More advanced DAX functions |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Sorting total sales into ascending order, using the RANKX function. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | More advanced DAX functions |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create two measures, showing average price for the South and all other regions. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | More advanced DAX functions |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Exclude a single animal from a pivot table, using CALCULATE combined with the VALUES function. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | The CALCULATE function |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Exclude a month from totals using the VALUES function to retain context. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Calculated columns |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Divide years into bands using SWITCH and calculated columns. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Calculated columns |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Calculate age bands for different dates using the SWITCH functon. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | The CALCULATE function |

Level: | Average difficulty |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use CALCULATE to work out the ratio of total sales to sales for a specific type of animal. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Date functions |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the PARALLELPERIOD function to show this quarter's total sales divided by the previous quarter's. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Date functions |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use the DATESYTD, TOTALYTD, SAMEPERIODLASTYEAR and various other date functions to compare totals. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Date functions |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Compare YTD totals for two years, showing variance. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Using other data sources |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Link to two Excel workbooks and one SQL Server table in PowerPivot. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | PowerPivot data models |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a pivot table with slicer based on ten different tables. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Power BI Desktop overview |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a matrix with conditional formatting and a tree diagram on two separate report pages. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Calculated columns |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Summarise sales by status of animal, using calculated columns. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Pivot tables using PowerPivot |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use a timeline to restrict a pivot table to 4 specific quarters. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | PowerPivot data models |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create two pivot tables, and two timelines which control both of the pivot tables. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | The CALCULATE function |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Use AVERAGEX to find average ratios, then CALCULATE to avoid divide-by-zero errors. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | Transforming data (Power Query) |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a query to import a table of tall buildings, create new columns and then pivot the data. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | Transforming data (Power Query) |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | For a table of skyscrapers, perform lots of query transforms and then pivot the results. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Measures |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Calculate ratio of area to units for shopping centres using AVERAGEX. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | The CALCULATE function |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Filter calculated sums to compare two stores' sales figures. |

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | The CALCULATE function |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Create a calculated column using EARLIER to show previous joiner count. |

Software: | PowerPivot |

Version: | Excel 2016 and later |

Topic: | More advanced DAX functions |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Group purchases into shopping centre size bands, using the EARLIER and FILTER functions. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | More advanced DAX functions |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Group sales into size bands using the EARLIER function. |

Software: | PowerPivot |

Version: | Excel 2013 and later |

Topic: | The CALCULATE function |

Level: | Harder than average |

Course: | PowerPivot / Excel Power BI |

Exercise: | Omitting a particular shopping centre from totals, using the VALUES function. |

You can search our full list of Power BI exercises here.