Let’s work with Kevin here on the fictitious Kevin Cookie Company’s data.

Have a look at the YouTube video called How to use Power Pivot in Excel | Full Tutorial. Kevin Stratvert works with three sets of information: Customers, Orders, and Cookies (products).

I followed along and came up with the following end result

What are some of the key takeaways from the video?

• Power Pivot is for analyzing data from multiple tables/sources
• Power Pivot can handle large volumes of data
• We start by getting some clean data
• If the data is not clean, we need Power Query, although this video doesn’t cover that
• The data here is common: Customers, Orders and Products
• The next thing is to create a data model with relationships
• In Power Pivot we click on Diagram View to create relationships
• We can create calculated columns
• For calculations, we can use multiplication, division, addition, and subtration
• We can even use numbers from more than one table to do calculations
• We can create measures and KPIs
• A measure might be an aggregation of many rows, such as a count of customers or total profit
• We can use Power Pivot to add measures by using DAX formulas
• Here we used DISTINCTCOUNT() and SUM()
• Total Profit:=SUM(Orders[Profit])
• Go to Excel, Insert, Pivot Table, From Data Model
• We have access to all of the columns in all of our tables as well as our measures
• We can get the customer name (from Customers) and count of orders (from Orders)
• We can define a KPI for total profit for example, which has a target value
• We can add a Pivot Chart
• We can add a slicer, perhaps for each product (cookie) type