Free PowerPivot exercises

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

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

Software: PowerPivot
Version: Excel 2013 and later
Topic: Power View
Level:
Course:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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. Or why not learn to do them the right way on one of our classroom Power BI training courses in the UK, or on one of our live online Power BI courses wherever you are in the world?

This page has 0 threads Add post