EXERCISE TOPIC▼

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

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