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

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Hierarchies |

Level: | |

Course: | |

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

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

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Calculated columns |

Level: | Relatively easy |

Course: | |

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Measures |

Level: | Average difficulty |

Course: | |

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Calculated columns |

Level: | Average difficulty |

Course: | |

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

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

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

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

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

Software: | PowerPivot |

Version: | Excel 2010 and later |

Topic: | Date functions |

Level: | Harder than average |

Course: | |

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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