Learning Data Analytics: Extending & Applying Core Knowledge

Your sales manager asks you to pull out the top 25 customers based on order amount. You are allowed to set the business rule. Which business rule makes the most sense with this ask?

Customers will be ranked by the highest single order amount.

You build a line total based on the Order Details table of OrderID, UnitPrice, and Qty. If you add your Customer List table, can you run a query for the top 50 customer orders?

No, because there is no join line between the two tables.

What is the first step in doing a custom sort in Power BI?

Create a custom table.

You are compiling the top 50 customers from last year. You run a query in Access for Customers along with an asterisk (*). What will be returned?

all the fields in the Customer table

You are using PowerPoint to create a tutorial on navigating through a dashboard. Why would you apply a shape and highlighting to one part of a visual?

so the user's attention is drawn to certain areas you want to focus on

Why is it often beneficial to unpivot data?

to have easy-to-read data visualizations

You want to explore your data after you append a folder with the latest data. Which load option will you select?

Combine and Transform Data

Where is noise found?

in a data set

You create both Wage Survey Reference and Wage Survey Duplicate data sets from your Wage Survey data set. If you make changes to the Wage Survey data set, where else will the changes occur?

in the Wage Survey Reference data set

You are working with ABC Corp. on its data analytics. The company asks you to compile a ranking of its top-performing products over the past five years. What two questions must you ask?

"Where is the data on all products?" and "What is your definition of top-performing?"

For a presentation, you want to sort customer orders for the quarter from largest to smallest, and also highlight orders that are larger than the average order amount. How will you do this?

After sorting, use the Above Average conditional formatting rule and select a highlighting color.

You do not want your soft filters to affect a particular visual on your dashboard. How will you prevent this from happening?

Edit the interactions.

When you are building a dashboard, why should you do the intricate sizing and formatting at the very end of the process?

Objects will shift a lot during the process of setting up a dashboard.

What is one benefit of pivot tables when it comes to exploring inside your data?

Pivot tools do not change the underlying data.

2020 sales for Product A are piquing your curiosity. How can pivots enable you to gain more insight into the data?

Pivots let you drill-down into the underlying data.

You are reviewing customers by yearly purchase amount over the last three years. Why would you add sparklines to your pivot table?

Sparklines show you trends over the three years of customers' purchases.

After you append data for missing orders to your invoices table, you notice 136 orders that were not invoiced. What is the most likely reason?

They are recent orders that have not yet been invoiced.

If you are building a dashboard for sales management, how will artifacts be involved?

They are the important visuals you will place on the dashboard.

How can cards, such as multicards, provide you with cleaner visuals?

They provide summary data and an easier way of sorting through data.

How can you use PowerPoint to make your presentations even more powerful?

You can create a step-by-step tutorial for users to explore the data on their own

You are going to present year-end sales information in a company-wide meeting. What must you consider when deciding how to present the data?

You know the data better than your audience, so present to the level of their understanding.

Your company recently moved to a new data storage system. As part of your monthly work, you check for any invoices that do not have a CustomerOrderID. You find 10. What does this most likely mean?

Some data from the old system did not move to the new system.

What is considered the "magic" of pivots?

You can determine what the X column and the Y row headings are.

You applied conditional formatting to Customer Order by Amount. After you did this, a colleague changed the sort to Alphabetical. What will happen?

Your conditional formatting will not be affected.

What is an example of an ad hoc report?

a request from Sales for how many customer purchase orders are in the system

You used the Steps Recorder in Windows to document the steps in changing a sort order. When colleagues use your recording, what will they see?

a series of screenshots with each screenshot showing a step you took

You suspect fewer customers are sending purchase orders to your company, although the dollar amount of the POs you receive has not changed. Which chart type can best visualize your hunch?

clustered column/line combo chart

You set up a pivot table for sales by product over the last three years and add an easy-to-read, 100% stacked bar chart. Why would you also include an Order Date slicer?

so users can look at particular months that interest them

You want a list of the total number of orders for all customers, even those with no orders. Your Customer and Order tables are in place. During your query, why will you go into Join Properties?

to have all customers returned

Your company decides to share dashboards with contracted salespeople via SharePoint. What must your company first determine before making this move?

whether all the contractors have the appropriate license