Power BI Pivot Table Exercise

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

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

To hide the red curve in the graph
To link the original table to the graph
To format the pivot table
To remove the growth percentage from the chart (correct)

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

Calculates the total sales of the current month
Formats the pivot table and chart
Returns the month, 12 months before the specified day (correct)
Compares the sales of the current quarter with the previous quarter

Why should the red curve be removed from the graph?

Because it shows negative percentages (correct)
Because it's difficult to format
Because it's not necessary for the presentation
Because it's not linked to the table

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

To switch the position of the tables (B) Signup and view all the answers

What does the YTD Sales formula calculate?

The total sales from the beginning of the year (A) Signup and view all the answers

What is the business case about?

Comparing sales of the quarters of 2022 with those of 2021 (A) Signup and view all the answers

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

To have one table for presentation and one for manipulation (B) Signup and view all the answers

What does the QTD Sales formula calculate?

The total sales of the current quarter (C) Signup and view all the answers

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

To store and analyze data (A) Signup and view all the answers

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

Post a brief explanation with a medical certificate on Canvas (B) Signup and view all the answers

Why is it important to practice Excel during your studies?

To prepare for your future job (D) Signup and view all the answers

What is the recommended way to ask questions in class?

By asking orally during the class (B) Signup and view all the answers

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

Use Google Translate (B) Signup and view all the answers

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 (B) Signup and view all the answers

What is the purpose of using PowerPivot in Excel?

To analyze data and create informative graphs (B) Signup and view all the answers

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

To have access to the software for future use (A) Signup and view all the answers

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

To show the growth of sales from one month to another (A) Signup and view all the answers

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 (D) Signup and view all the answers

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

To verify the relations between tables still exist (D) Signup and view all the answers

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

To show the percentage change in sales from one month to another (B) Signup and view all the answers

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

All linked tables are selected automatically (C) Signup and view all the answers

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

Some numbers are not easy to interpret (B) Signup and view all the answers

Why is the order of selection important when selecting tables?

Because the order affects the resulting data (C) Signup and view all the answers

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 (D) Signup and view all the answers

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

To expand or collapse all data in one move (A) Signup and view all the answers

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 (D) Signup and view all the answers

How do you create a margin column in PowerPivot?

By using the 'Measures' feature and selecting the 'Orders' table (A) Signup and view all the answers

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

To show the difference between the current and previous month's sales (C) Signup and view all the answers

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

Because it allows managers to make informed decisions (D) Signup and view all the answers

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

To analyze the profitability of products (A) Signup and view all the answers

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

It pre-calculates the sum of sales and cost (B) Signup and view all the answers

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

To have a unique list of products (A) Signup and view all the answers

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

To deliver data for the Products table (C) Signup and view all the answers

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 (B) Signup and view all the answers

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

ProductID (B) Signup and view all the answers

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 (D) Signup and view all the answers

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 (D) Signup and view all the answers

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 (B) Signup and view all the answers

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 (B) Signup and view all the answers

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

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

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.