Power BI Pivot Table Exercise

39 Questions

What is the purpose of unchecking 'growth previous quarter' in the pivot table?

To remove the growth percentage from the chart

What is the function of the PARALLELPERIOD function in the formula?

Returns the month, 12 months before the specified day

Why should the red curve be removed from the graph?

Because it shows negative percentages

What is the purpose of dragging and dropping the tables in the pivot table?

To switch the position of the tables

What does the YTD Sales formula calculate?

The total sales from the beginning of the year

What is the business case about?

Comparing sales of the quarters of 2022 with those of 2021

What is the purpose of creating a duplicate table in the pivot table?

To have one table for presentation and one for manipulation

What does the QTD Sales formula calculate?

The total sales of the current quarter

What is the primary purpose of using Excel in a company?

To store and analyze data

What should you do if you are behind schedule with assignments due to illness?

Post a brief explanation with a medical certificate on Canvas

Why is it important to practice Excel during your studies?

To prepare for your future job

What is the recommended way to ask questions in class?

By asking orally during the class

What should you do if you don't understand an English word?

Use Google Translate

What is the benefit of completing weekly assignments on time?

To improve your chances of getting a high score on the exam if you're close to the pass mark

What is the purpose of using PowerPivot in Excel?

To analyze data and create informative graphs

Why is it important to install Excel 365 on your laptop or home computer?

To have access to the software for future use

What is the primary objective of creating a table to evaluate sales evolution?

To show the growth of sales from one month to another

What is the first step to take when your teacher's model points to sources on their computer?

Edit the data source and browse to the source on your computer

Why is it important to check the 'Diagram View' after updating the data source?

To verify the relations between tables still exist

What is the purpose of creating a measure 'Growth previous month'?

To show the percentage change in sales from one month to another

What happens when you select only one table in the 'tables' tab?

All linked tables are selected automatically

What is one of the problems with the table created in the exercise?

Some numbers are not easy to interpret

Why is the order of selection important when selecting tables?

Because the order affects the resulting data

Why is a large percentage growth in sales not necessarily a good thing?

Because it may be due to a low sales figure in the previous month

What is the purpose of the 'Expand/Collapse' feature in PowerPivot?

To expand or collapse all data in one move

What is the main reason why tables and graphs should be designed to be easy to understand?

Because they should be useful for managing the business

How do you create a margin column in PowerPivot?

By using the 'Measures' feature and selecting the 'Orders' table

What is the purpose of creating a measure 'Compare previous month'?

To show the difference between the current and previous month's sales

Why is it important to show numbers that are understandable in a table?

Because it allows managers to make informed decisions

What is the purpose of creating a margin column in PowerPivot?

To analyze the profitability of products

What is the benefit of using PowerPivot to create a margin column?

It pre-calculates the sum of sales and cost

What is the primary reason for adding products as a separate table?

To have a unique list of products

What is the purpose of the 'products.txt' file?

To deliver data for the Products table

Why is it necessary to draw an arrow when linking the Products table to the Orders table?

To establish a relationship between the two tables

What is the unique identifier of a product in the Products table?

ProductID

What happens if there are duplicate values in the Products table?

An error message will appear, indicating that the outer table does not contain 1 row per product

How can the issue of duplicate values in the Products table be resolved?

By recreating the Products table and making sure every row is unique

What is the implication of using a model that is imported from and linked to external data sources?

The source data should be corrected if there are errors

What is the final step after resolving the issue of duplicate values in the Products table?

Refresh the data in the sheet and integrate the Products table into the model

Study Notes

Pivot Tables and PowerPivot

Create a pivot table using the formula: Calculate([Sum of Sales]; PREVIOUSMONTH('Calendar'[Date]))
Add the field to the pivot table and create measures:
- "Compare previous month" = [Sum of Sales] - [Sales previous Month]
- "Growth previous month" = ([Sum of Sales] - [Sales previous Month]) / [Sales previous Month]

Evaluating the Table

The table is useful for managers to see the evolution of sales from month to month
The table has some issues:
- Some numbers are not easy to interpret (e.g. 84.332 shows twice)
- There is a repetition: bottom = top
- At least one percentage is very big (due to a month with few sales)

Excel and PowerPivot

Spreadsheets and graphs are important tools in companies
Excel is a handy software that helps to herd data
Practice using Excel to create high-quality solutions and deliver them timely

Assignments and Tips

Complete assignments weekly and ask questions during classes
Use "Google translate" to understand English words
If behind schedule due to illness or special circumstances, write a brief explanation and post it on Canvas
Make sure to complete and post all assignments as soon as possible

Creating a Pivot Table

Choose a table, and linked tables will be selected automatically
Select CustomerCountry and then Customer to get the desired layout
Add "Margin" to the pivot table using the formula: [Sum of Sales] - [Sum of Cost]
Use PowerPivot/"Measures"/New to create the margin column

Customizing a Pivot Table and Chart

Use the filter to check which table is linked to the graph
Remove the red curve to make the graph more readable
Use drag and drop to switch the position of tables
Uncheck "growth previous quarter" to customize the pivot table and chart

Special Time Periods

YTD (Year To Date) = TOTALYTD([Sum of Sales];Calendar[Date])
QTD (Quarter To Date) = TOTALQTD([Sum of Sales];Calendar[Date])
Month Sales Last Year = Calculate([Sum of Sales]; PARALLELPERIOD('Calendar'[Date];-12;Month))
Compare parallel month = [Sum of Sales] - [Month Sales Last Year]
Growth parallel month = [Compare parallel month] / [Month Sales Last Year]

Business Case 1

Compare sales of quarters of 2022 with those of 2021
Add products as a separate table using the text file "products.txt"
Import the file into the model and draw an arrow from Orders to Products
Make sure the data is linked correctly and solve any errors that may occur

Create a pivot table in Power BI to calculate sales for the previous month. This exercise helps users practice their data analysis skills in Power BI.

Make Your Own Quizzes and Flashcards

Convert your notes into interactive study material.