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.

Go to exercise ...

Software: PowerPivot
Version: Excel 2010 and later
Topic: Hierarchies
Level:
Course: PowerPivot / Excel Power BI
Exercise: Create a pivot table using two hierarchies.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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.

Go to exercise ...

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

This page has 0 threads Add post