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: 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. 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