Power Pivot And Power Bi: The Excel User’s Guide To Dax, Power Query, Power Bi & Power Pivot In Excel 2010

--- Bài mới hơn ---

  • Hướng Dẫn Cách Cài Đặt Power Query Trong Excel 2010, 2013, 2022
  • Hướng Dẫn Cách Cài Đặt Power Pivot Trong Thanh Menu Của Excel
  • Enabling Power Pivot Excel 2013
  • Pivot Table Trong Excel 2007, 2010, 2013
  • Cách Lập Báo Cáo Theo Tháng Bằng Pivot Table Khi Dữ Liệu Không Có Sẵn Cột Tháng
  • Microsoft Power BI, including Power Pivot and Power Query, are a set of free add-ons to Excel that allow users to produce new kinds of reports and analyses that were simply impossible before.

    This book, printed in full-gorgeous color, gives you an overview of Power BI, Power Pivot and Power Query, and then pes into DAX formulas, the core capability of Power Pivot. Always from the perspective of the Excel audience.

    Written by the world’s foremost Power BI bloggers and practitioners, the book’s concepts and approach are introduced in a simple, step-by-step manner tailored to the learning style of Excel users everywhere. The techniques psented allow users to produce, in hours or even minutes, results that formerly would have taken entire teams weeks or months to produce.

    This book includes lessons on:-

    • difference between calculated columns and measures
    • how formulas can be reused across reports of completely different shapes
    • how to merge disjointed sets of data into unified reports
    • how to make certain columns in a pivot behave as if the pivot were filtered while other columns do not
    • how to create time-intelligent calculations in pivot tables such as “Year over Year” and “Moving Averages” whether they use a standard, fiscal, or a complete custom calendar.
    • how to leverage Power Query to make your Power Pivot models awesome!
    • how to use Power BI Desktop and chúng tôi and how they fit into the Excel landscape

    The “pattern-like” techniques and best practices contained in this book have been developed and refined over several years of onsite training with Excel users around the world, and the key lessons from those seminars costing thousands of dollars per day are now available to you, within the pages of this easy-to-follow guide. This updated second edition covers new features introduced with Office 2022 and Power BI Desktop.

    At PowerPivotPro, I have really enjoyed working with the Power Pivot and Power BI community. This book, with Rob Collie, was an effort to reach out and help an even greater audience. Little did I know what I was signing up for 🙂

    It was hard juggling book writing with the training/consulting/travel. But I am glad we persevered. Big thanks to our IndieGoGo crowdfunding supporters, who not only made it possible to print this book in full-gorgeous color, but also inspired us to burn the night oil in writing those last chapters.

    I sincerely believe that Power Pivot and Power BI can transform the lives of Excel users worldwide – after all I was one of them. But change is hard, even when it’s for the better. We hope we can help you go from Excel to Power BI with this book and our services at PowerPivotPro.

    Power On!

    -Avi Singh

    Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data. We have repeatedly witnessed the “Power tools” transformative impact on the bottom line – far more robust and elegant than Excel alone, and much more agile and affordable than traditional BI tools. Their shared state of the art calculation engines (DAX and M) turn any PC into an analytical supercomputer, but the real secret is people – these tools are 100% learnable by today’s users of Excel. The data revolution lays not in the hands of an elite few, but in YOUR hands, and we want to help you seize that opportunity.

    Rob Collie is an entrepneur, author, and consultant. He was one of the founding engineers behind Power Pivot at Microsoft and operates the world’s leading Power Pivot/Power BI website, chúng tôi He lives in Indianapolis. Avichal Singh (Avi) is Principal Consultant at PowerPivotPro where he blogs, trains and consults on PowerPivot and Power BI. His past experience includes building large scale Power BI solutions at Microsoft. He lives in Seattle.

    --- Bài cũ hơn ---

  • Không Thể Cài Đặt Power Pivot Trong Excel 2013
  • Pivottable Là Gì? Hướng Dẫn Dùng Pivottable Trong Excel
  • Cách Sử Dụng Pivot Table Cơ Bản, Nâng Cao Để Thống Kê Dữ Liệu Báo Cáo Trong Excel
  • Tất Tần Tật Cách Sử Dụng Pivot Table Excel 2013 Từ Cơ Bản Đến Nâng Cao
  • Cách Làm Mới Dữ Liệu Bảng Pivot Trong Excel 2013
  • Introduction To Power Pivot – Excel Exposure

    --- Bài mới hơn ---

  • Cách Xóa Pivottable Trong Excel
  • How To Install Power Query
  • How To Use Find And Replace In Microsoft Word To Make Quick Edits To A Document
  • Hướng Dẫn Cách Sử Dụng Hàm Replace Trong Excel 2007, 2010, 2013, 2022
  • Cách Sử Dụng Excel 2022 – Cách Dùng Find, Select & Replace Trong Excel
  • Here’s a helpful guest lesson about an incredibly useful Excel add-in called Power Pivot.

    Thanks to Nick Williams from Acuity Training for creating this helpful post!!!

    Power Pivot is an Excel add-in which can used to perform powerful data analysis and create sophisticated data models. It can handle large volumes of data (millions of rows) from various sources and all of this within a single Excel file.

    Power Pivot is basically a SQL Server Analysis Services engine made available using an in-memory process that runs directly within Excel. It is commonly referred to as an Internal Data Model. The most effective way to interact with the Internal Data Model is to use the Power Pivot Ribbon interface.

    Once the Power Pivot add-in is installed and available, you can create a Data Model, which is a collection of tables with relationships. Any data you import into Excel or already have in Excel, once added to the data model is available in the Power Pivot window. The Power Pivot Ribbon gives you additional functions over and above the standard Excel Data tab.

    The Power Pivot add-in is available in Excel 2010, and is native in Excel 2013 and 2022. However, only the following versions of Excel 2022 support the ‘Power Query’ functionality:

    • Excel 2022 – Office 365 ProPlus
    • Excel 2022 – Office 365 E3
    • Excel 2022 – Office 365 E4 andE5

    To give you a feel for where Power Pivot fits in when using Excel for data analysis or visualization, let’s first have a quick look at how Power Pivot fits into the overall Business Intelligence process and how it works with the other BI tools in Excel.

    Power Pivot acts as a data model, this means that the first step is to import some data. Unless it is already in your Excel sheet you will need a tool or connector to connect to different types of data sources and fetch your data. This can be a complex subject depending on your data source and is beyond the scope of this article.

    After fetching the data, you will probably need to do some cleaning and transformation on it. Both these functions in Excel are carried out by another add-in called Power Query (in Excel 2010 and 2013) / Get & Transform (in Excel 2013).

    The final step is creating the Power Pivot data model. This is where we create the relationships between different data tables.

    You can create simple measures or Key Performance Indicators (KPIs) in Power Pivot.

    Finally one you have all of the required metrics calculated, you can summarize the information in your Power Pivot data model using Pivot Tables and / or Pivot Charts. The combination of multiple Pivot Tables / Pivot Charts with slicers can be used to create a Dashboard

    Lets look at how you launch Power Pivot.

    To install the Power Pivot add-in:

    The green Manage icon launches the Power Pivot window. Calculated Fields and KPIs can be used to create any summarizing calculations. Add to Data Model allows you to add the data table psent in the Excel into Power Pivot. Update All allows you to update all the data connections established with the data model. Settings helps to control other parameters associated with Power Pivot.

    Example 1: Adding Data to the Data Model

    The first step is to add some data into the data model. Data sources fall into two broad categories. First, it comes via an import from an external data source. Second it is psent in the Excel tables in the current workbook.

    For now, let us take an example which shows how to work with Excel tables in the current Excel workbook to Power Pivot. Use the below Excel workbook to follow along:

    1. Once you have saved the file and opened that, you’ll notice that there are two tabs. One, data tab and the other mapping tab.
    2. The data tab has the sales info as shown in the screen shot below. The data tab contains detailed data showing sales by country, by product type, by quarter, by sales channel etc. The mapping tab contains that data sub-totaled by quarter, also seen below.

      Data Tab:

      Mapping Tab:

    3. The mapping tab has a table showing a method of “mapping” or a lookup table that allows you to change from the quarter notation to the year and/or the quarter. This can also be derived using formula in Power Pivot as well!
    4. First, let’s see how we can add the two tables into the data model. Make sure you have selected the range of the Data table then select the Add to data model button from the Power Pivot Tab:

    5. You’ll notice a new pop-up window opens. It is the Power Pivot window, it has several options to edit, modify and update the data model

    6. Go back to the Excel file and Repeat the steps 4 and 5 with the data in the Mapping tab to add that to the data model.

    7. Please note that for each data added onto the Data Model, a new tab is created in the Power Pivot tab

    Creating Relationships

    Now that we have two datasets added to the data model. Let’s look at how we can create a relationship between the two.

    1. The common field between the two datasets will be used to create the relationship between them
    2. In this example, Quarter from the Data table is the same as QuarterCode from the Mapping table
    3. This opens a pop-up where you’ll need to select the main table, the lookup table and the corresponding columns
    4. In the current example, the Data table is our main table and the common column is The Mapping table is our Lookup Table and the Related Lookup Column is QuarterCode. Hence the following selections needs to be made in the pop-up

    5. It shows the relationship created, as shown below

    You can create a relationship in Diagram View once you get more comfortable with this process.

    Now we’ve created a very simple data model let’s look at creating some calculated columns. For more on

    Calculated Columns in Power Pivot

    In this section, you’ll learn how to create calculated columns in Power Pivot.

    First make sure that you are in the Mapping tab of the Power Pivot window

    1. Now, let’s explore how we can derive the year and quarter using just the Quartercode column in the Mapping tab to keep this example very simple.
    2. In the default view, you can see there is a column as “Add Column” as the Header of a column which doesn’t contain any data.
    3. Almost all of the formulas in Power Pivot are the same as they are in Excel.

      In this example, first we take the first four characters from the QuarterCode using the LEFT function and then use the VALUE function to convert the string of characters into a number. See this video for more details on working with text functions.

      1. Next we add another column called “Quarter Derived” and use the following formula

      =VALUE(RIGHT([QuarterCode],1))

      Similar to the formula in the “Year Derived” column, this formula takes the last digit of the string in the QuarterCode column and converts it into a number.

    --- Bài cũ hơn ---

  • Excel 2022: Intro To Pivottables
  • Danh Sách Tổng Hợp Các Phím Tắt Excel 2022
  • Không Mở Được File Excel
  • Hướng Dẫn Cách Dùng Hàm Sumif Trong Excel: Học Với Công Thức, Cách Sử Dụng Và Ví Dụ
  • Cách Sửa Lỗi #value! Khi Sử Dụng Hàm Countif / Countifs Trong Excel
  • Enabling Power Pivot Excel 2013

    --- Bài mới hơn ---

  • Pivot Table Trong Excel 2007, 2010, 2013
  • Cách Lập Báo Cáo Theo Tháng Bằng Pivot Table Khi Dữ Liệu Không Có Sẵn Cột Tháng
  • Cách Cài Đặt Power Query Trong Excel 2010, Excel 2013, Excel 2022
  • Chuẩn Hoá Kiểu Dữ Liệu “khó Đỡ” Với Công Cụ Power Query Trong Excel
  • Khóa Học Phân Tích Báo Cáo Bằng Power Query Trong Excel Để Bán Hàng Thành Công
  • Power Pivot Excel 2013 is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel.

    Read the tutorial on how to install Power Pivot in Excel 2010 here Read the tutorial on how to enable Power Pivot in Excel 2022 here

    In this tutorial, you will be provided with a detailed guide on

    Let’s go through each point one-by-one!

    What is Power Pivot?

    In a nutshell, PowerPivot Excel 2013 allows you to use multiple data sources for analysis.

    Power Pivot gives you the power to work with large sets of data that are over 1 million rows!!! It lets you create models, establish relationships and create calculations.

    You could import, merge and perform analysis on the resulting data. The beautiful thing with Power Pivot is it allows you to work on Big Data with no limitations.

    Imagine getting data from multiple sources like SQL Server, Oracle, XML, Excel, Microsoft Access, and analyzing these all into one awesome Pivot Table!

    How to Enable Power Pivot for Excel 2013?

    So how do I get my hands on this super awesome add-in…I hear you say?

    You need

    The good thing with Power Pivot is it already comes with your Excel 2013, we need to enable it to start using Power Pivot.

    You just need to make sure you have Office Professional Plus 2013 as either:

    To work on Power Pivot, you must first understand how to open the Power Pivot window.

    How to open the Power Pivot window?

    Adding Data to Data Model

    The first step is to add some data to your model. You can import data from two categories:

    Let’s talk about each one of them in detail.

    To Import data from the Current Workbook, follow the steps below:

    STEP 1: Highlight the Data in your current workbook.

    There are two views that are available in Power Pivot – Data View and Diagram View.

    Data View

    This is the view shown to Excel users by default.

    Data view displays a table of your data model in a grid format, much like the standard Excel. Each table has its own tab at the bottom of the sheet. You cannot make any edits in the cells in the data view.

    A data view is comprised of two parts – the Table area and the Calculation area.

    Let’s look at an example of how data is displayed in Diagram View.

    You have two tables – Names and Sales.

    *** You can view the exact step-by-step tutorial of how this was done here: Linking Excel Tables in Power Pivot. ***

    You can drag the corner of the table to resize them and can also move them to different locations.

    Create Relationship in Power Pivot

    In the example above, you have imported the two tables in the Power Pivot window. You can now link the ID column from the Names table with the ID column in Sales Table.

    Let’s see how it can be done by following the steps below:

    Create Pivot Table using Power Pivot data

    • You can work with millions of rows and extract data from multiple sources.
    • You can process calculations and analysis faster.
    • You can import data from multiple sources and you can also filter data and rename columns while importing.
    • When you import a table in Power Pivot, each table gets organized in inpidual tabbed pages.
    • You can create your own formula using DAX expssion.
    • You can create relationships among the table to easily analyze data fields together.
    • You can create calculated fields and calculated columns in the data table.

    Conclusion

    HELPFUL RESOURCE:

    You can learn more about how to use Excel by viewing our FREE Excel webinar training on and !

    Bryan is a best-selling book author of the 101 Excel Series paperback books.

    --- Bài cũ hơn ---

  • Hướng Dẫn Cách Cài Đặt Power Pivot Trong Thanh Menu Của Excel
  • Hướng Dẫn Cách Cài Đặt Power Query Trong Excel 2010, 2013, 2022
  • Power Pivot And Power Bi: The Excel User’s Guide To Dax, Power Query, Power Bi & Power Pivot In Excel 2010
  • Không Thể Cài Đặt Power Pivot Trong Excel 2013
  • Pivottable Là Gì? Hướng Dẫn Dùng Pivottable Trong Excel
  • Enabling Power Pivot Excel 2022

    --- Bài mới hơn ---

  • Lý Do Sử Dụng Microsoft Power Query Cho Excel
  • Tổng Quan Power Query: Giới Thiệu Về Công Cụ Dữ Liệu Mạnh Nhất Của Excel
  • Cách Kết Hợp Nhiều Bộ Dữ Liệu Trong Microsoft Excel Bằng Power Query
  • Hướng Dẫn Sử Dụng Power Query Để Chuẩn Hoá Dữ Liệu
  • Cách Sử Dụng Hiệu Quả Find Và Replace Nâng Cao Trong Excel
  • Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel.

    Read the tutorial on how to install Power Pivot in Excel 2010 here Read the tutorial on how to enable Power Pivot in Excel 2022 here

    In this tutorial, you will be provided with a detailed guide on

    Let’s go through each point one-by-one!

    What is Power Pivot?

    Power Pivot gives you the power to work with large sets of data.

    In a nutshell, it allows you to use multiple data sources. Then you could import, merge and perform analysis on the resulting data.

    The beautiful thing with Power Pivot is it allows you to work on Big Data with no limitations.

    Imagine getting data from multiple sources like SQL Server, Oracle, XML, Excel, Microsoft Access then build a Data Model from it. Then you can analyze these all into one awesome Pivot Table!

    How to Enable Power Pivot for Excel 2022?

    So how do I get my hands on this super awesome add-in…I hear you say?

    The good thing with Power Pivot is it already comes with your Excel 2022, we need to enable it to start using Power Pivot.

    You just need to make sure you have one of these versions:

    Office 365 Pro Plus

    Power Pivot is NOT AVAILABLE in Office 2022 for the following products:

    To work on Power Pivot, you must first understand how to open the Power Pivot window.

    How to open the Power Pivot window?

    Adding Data to Data Model

    The first step is to add some data to your model. You can import data from two categories:

    Let’s talk about each one of them in detail.

    To Import data from the Current Workbook, follow the steps below:

    STEP 1: Highlight the Data in your current workbook.

    There are two views that are available in Power Pivot – Data View and Diagram View.

    Data View

    This is the view shown to Excel users by default.

    Data view displays a table of your data model in a grid format, much like the standard Excel. Each table has its own tab at the bottom of the sheet. You cannot make any edits in the cells in the data view.

    A data view is comprised of two parts – the Table area and the Calculation area.

    Let’s look at an example of how data is displayed in Diagram View.

    You have two tables – Names and Sales.

    *** You can view the exact step-by-step tutorial of how this was done here: Linking Excel Tables in Power Pivot. ***

    You can drag the corner of the table to resize them and can also move them to different locations.

    Create Relationship in Power Pivot

    In the example above, you have imported the two tables in the Power Pivot window. You can now link the ID column from the Names table with the ID column in Sales Table.

    Let’s see how it can be done by following the steps below:

    Create Pivot Table using Power Pivot data

    • You can work with millions of rows and extract data from multiple sources.
    • You can process calculations and analysis faster.
    • You can import data from multiple sources and you can also filter data and rename columns while importing.
    • When you import a table in Power Pivot, each table gets organized in inpidual tabbed pages.
    • You can create your own formula using DAX expssion.
    • You can create relationships among the table to easily analyze data fields together.
    • You can create calculated fields and calculated columns in the data table.

    Conclusion

    HELPFUL RESOURCE:

    You can learn more about how to use Excel by viewing our FREE Excel webinar training on and !

    About The Author

    Bryan is a best-selling book author of the 101 Excel Series paperback books.

    --- Bài cũ hơn ---

  • Nâng Cấp Mô Hình Dữ Liệu Power Pivot Cho Excel 2013 Hoặc Excel 2022
  • Tạo Pivottable Để Phân Tích Dữ Liệu Trang Tính
  • Sử Dụng Công Cụ Pivottable Và Pivotchart Phân Tích Dữ Liệu Trên Excel
  • Cách Dùng Hàm Rank Để Sắp Xếp Thứ Hạng Trong Excel
  • Hướng Dẫn Cách Tính Trung Bình Bằng Hàm Average Trong Excel Nhanh Chóng
  • Không Thể Cài Đặt Power Pivot Trong Excel 2013

    --- Bài mới hơn ---

  • Power Pivot And Power Bi: The Excel User’s Guide To Dax, Power Query, Power Bi & Power Pivot In Excel 2010
  • Hướng Dẫn Cách Cài Đặt Power Query Trong Excel 2010, 2013, 2022
  • Hướng Dẫn Cách Cài Đặt Power Pivot Trong Thanh Menu Của Excel
  • Enabling Power Pivot Excel 2013
  • Pivot Table Trong Excel 2007, 2010, 2013
  • Tôi có máy Windows 10 Enterprise Citrix đã cài đặt Microsoft Office Excel 2013 Professional Plus .

    Tôi thấy tùy chọn Power View và một vài thứ khác nhưng không có gì phù hợp với Power Pivot .

    Tôi đã gặp lỗi này khi cố gắng cài đặt một bổ trợ:

    Thiết lập Microsoft SQL Server 2012 PowerPOLL cho Excel Thiết lập thiếu các điều kiện tiên quyết: Bổ trợ này yêu cầu Excel 2010. Nếu bạn có Excel 32 bit, bạn phải cài đặt phiên bản bổ trợ ene 32 bit. Nếu không, bạn phải cài đặt phiên bản 64 bit.

    Sửa lỗi PowerPOLL trong Excel 2013

    Như đã nêu ở đây. . .

    Như bạn đã nói bạn là người dùng gia đình, tôi khuyên bạn nên mua phiên bản độc lập Excel 2013 có thể tải xuống. Nếu bạn cài đặt Excel 2013 độc lập và cập nhật lên phiên bản 1511, bạn sẽ nhận được Power Pivot, điều này sẽ tự động xảy ra.

    Thông tin thêm về các phiên bản văn phòng có tính năng Power BI vui lòng tham khảo bài viết này.http://blog.crossjoin.co.uk/2013/02/01/office-2013-office-365-editions-and-bi-features/nguồn

    Bạn nên xác nhận rằng bản dựng Excel 2013 của bạn là phiên bản 1511 hoặc mới hơn có chức năng này để đảm bảo bạn có phiên bản nơi bạn thực sự có thể sử dụng các tính năng PowerPOLL.

    Giải pháp đã làm việc cho người khác. . .

    nguồn

    Để giải quyết vấn đề này, hãy chạy trình khắc phục sự cố thích hợp cho bổ trợ:

    PowerPOLL

    • Trình khắc phục sự cố bổ trợ PowerPOLL có thể tự động khắc phục sự cố được mô tả trong bài viết này. Trình khắc phục sự cố này khắc phục nhiều sự cố.

      run now

    • Trình khắc phục sự cố bổ trợ Power View có thể tự động khắc phục sự cố được mô tả trong bài viết này. Trình khắc phục sự cố này khắc phục nhiều sự cố.

      run now

    Để khắc phục sự cố này, hãy làm theo các bước sau để xóa các khóa đăng ký bị ảnh hưởng:

    Ghi chú

    • Excel 2013 và Excel 2022 tự động xây dựng lại các khóa đăng ký.
    • Các khóa đăng ký mà bạn phải xóa khác nhau, tùy thuộc vào bổ trợ mà bạn sử dụng. Bạn phải xóa các khóa đăng ký chỉ cho bổ trợ bị thiếu trong hộp thoại Bổ trợ COM. Mỗi bổ trợ tương ứng với các khóa đăng ký sau, tương ứng:

    Bổ trợ Microsoft Office PowerPOLL cho Excel 2013

    Bổ trợ Microsoft Office PowerPOLL cho Excel 2022

    Thêm thông tin

    Quan trọng: Tất cả các bổ trợ và bổ trợ Yêu cầu đều yêu cầu SKU cụ thể của Microsoft Office 2013 và Microsoft Office 2022. Chúng có sẵn trên:

    • Microsoft Office 2013 Professional Plus và Microsoft Office Professional Plus 2022
    • Microsoft Office 365 ProPlus có sẵn dưới dạng thuê bao độc lập.
    • Microsoft Office 365 ProPlus có sẵn như một phần của các dịch vụ Office 365 Enterprise E3, Office 365 Enterprise E4, Office 365 Education E2, Office 365 Education E3, Office 365 Chính phủ E3 hoặc Office 365 Chính phủ E4.
    • Microsoft Excel 2013 độc lập với bản cập nhật sau đây http://support.microsoft.com/kb/2817425

    nguồn

    Tài nguyên khác

    --- Bài cũ hơn ---

  • Pivottable Là Gì? Hướng Dẫn Dùng Pivottable Trong Excel
  • Cách Sử Dụng Pivot Table Cơ Bản, Nâng Cao Để Thống Kê Dữ Liệu Báo Cáo Trong Excel
  • Tất Tần Tật Cách Sử Dụng Pivot Table Excel 2013 Từ Cơ Bản Đến Nâng Cao
  • Cách Làm Mới Dữ Liệu Bảng Pivot Trong Excel 2013
  • Tổng Quan Về Pivottable Và Pivotchart
  • Power Pivot For Excel: What It Is And How To Use It

    --- Bài mới hơn ---

  • Hướng Dẫn Cách Phân Biệt Giữa Power Pivot Và Pivot Table
  • How To Use Powerpivot In Excel: The Ultimate Guide
  • Power Pivot For Excel Tutorial: Top Use Cases And Examples
  • How To Refresh Pivot Table In Excel (Manually + Auto
  • How To Refresh Pivot Table When Data Changes In Excel?
  • You’ve got data and lots of it. When you want to analyze all that data, learn how to use the Power Pivot add-in with Excel to import data sets, identify relationships, build PivotTables, and create PivotCharts.

    How to Get the Excel Power Pivot Add-in

    Power Pivot gives you the power of business insights and analytics app. You don’t need specialized training to develop data models and perform calculations. You just need to enable it before you can use it.

    Follow Along with the Tutorial

    When you want to get up and running with Power Pivot quickly, learn by example. Microsoft has several example datasets available as a free download, which contain the raw data, the Data Model, and examples of data analysis. These are great learning tools that provide insight into how professionals analyze big data.

    This tutorial uses the Microsoft Student Data Model sample workbook. You’ll find a download link to the sample workbook and to a completed data model in the first note on the page.

    The data in this sample Excel workbook has the following:

    • The workbook contains four worksheets.
    • Each worksheet contains related data, meaning there’s at least one column heading on a worksheet matching a column heading in another worksheet.
    • The data in each worksheet is formatted as a table.
    • Every cell in the table contains data. There are no blank cells, rows, or columns in the tables.

    There are other example datasets on the Microsoft website. Explore these learning resources:

    • Download data from a Microsoft Access database that describes Olympic Medals.
    • Download three Business Intelligence samples that show how to use Power Pivot to import data, create relationships, build PivotTables, and design PivotCharts.

    How to Add Data to Your Excel File and Build a Data Model

    You’ve collected the data you’ll need. Now it’s time to import your data sets into Excel and automatically create a Data Model. A Data Model is similar to a relational database and provides the tabular data used in PivotTables and PivotCharts.

    To import Excel data into a Power Pivot Data Model:

    1. Open a blank worksheet and save the file with a unique name.

    2. Select the tables you want to import.

    3. To make sure the import was successful and the Data Model was created, go to Data and, in the Data Tools group, select Go to the Power Pivot Window.

    4. The tabs at the bottom of the Power Pivot Window correspond to each of the tables that were imported.

    5. Close the Power Pivot Window.

    Create Relationships Between Tables with Power Pivot Excel

    Now that you have a Data Model, it’s time to create relationships between each of the data tables.

    How to Create PivotTables

    When you use Power Pivot to create a Data Model, most of the hard work involving PivotTables and PivotCharts has been done for you. The relationships you created between the tables in your dataset are used to add the fields you’ll use to create PivotTables and PivotCharts.

    1. In the Create PivotTable dialog box, select New Worksheet, then select OK.

    2. To sort the PivotTable data, drag a field to the Filters area. In this example, the Class Name field is added to the Filters area so the list can be filtered to show student’s average grade for a class.

      To change the calculation method used by a field in the Values area, select the dropdown box next to the field name and select Value Field Settings. In this example, Sum of Grade was changed to Average of Grade.

    3. Analyze your data. Experiment with the filters and sort data using the column header dropdown arrows.

    Convert a PivotTable into a PivotChart

    If you want to visualize your PivotTable data, turn a PivotTable into a PivotChart.

    1. Select Pivot Chart to open the Insert Chart dialog box.
    2. Choose a chart, then select OK.

    Create PivotCharts

    If you’d rather analyze your data in a visual format, create a PivotChart.

    1. In the Power Pivot window, select Home, then select the PivotTable dropdown arrow. A list of options appears.

    2. Analyze your data. Experiment with the Filters and sort data with the column header dropdown arrows.

    Thanks for letting us know!

    --- Bài cũ hơn ---

  • Hướng Dẫn 200 Phím Tắt Excel Trên Máy Tính Window Và Macbook
  • How To Use The Excel Pivot Table Field List
  • Hướng Dẫn Cách Ẩn Các Nút Và Các Nhãn Của Pivot Table Trong Excel
  • Bảng Tổng Hợp Trong Excel
  • How To Change A Pivot Chart
  • The Right Version Of Excel 2013 For Using Powerpivot #powerpivot #excel

    --- Bài mới hơn ---

  • Create A Pivot Table In Excel
  • Hướng Dẫn Cách Xóa Nhanh Một Trường Pivot Table Trong Excel
  • How To Delete A Pivot Table In Excel (Easy Step
  • How To Delete A Pivot Table In Excel
  • How To Delete One Or All Pivot Tables In Excel?
  • UPDATE Aug 16, 2013: Excel 2013 stand-alone now includes Power Pivot (now the name has a space and it is no longer a single word!). This post was originally published on Feb 18, 2013 and is now outdated. If you already have Excel 2013 stand-alone, you should see PowerPivot enabled in an upcoming update.

    Many people started using PowerPivot with Excel 2010. In order to start using PowerPivot for Excel 2010, you just have to download the add-in and install it for free. In Excel 2013, PowerPivot is already installed and you just have to enable it. However, you have to be careful about the Excel 2013 version you use, because not all the versions have all the features available.

    • Data Model features available in all Excel 2013 versions
      • Internal xVelocity engine
      • Load multiple tables in a data model
      • Create relationships
      • Navigate a data model with multiple tables using a single PivotTable
      • Use only implicit measures
    • PowerPivot features available in selected versions of Office 2013 (*)
      • Create calculated columns
      • Create calculated fields
      • Use PowerPivot window and all the other features available there
      • Also Power View is available only in these versions of Excel

    So what are the version of Office 2013 that enable the usage of PowerPivot features?

    Here is the list:

    • Office Professional Plus 2013 via Open, Select or EA
    • Excel 2013 stand-alone UPDATE Aug 16, 2013 – any stand-alone version of Excel 2013 has PowerPivot
    • Office 365 ProPlus via Office 365 (www.office365.com) Subscription when it becomes available (February 27, 2013)

    The only way to get these full BI features is through a Microsoft Volume License Agreement or Office 365 service. If you are not included in a Microsoft Volume License Agreement, the only way to get a copy of Excel 2013 that has all PowerPivot and Power View features available is getting an Office 365 ProPlus subscription.

    UPDATE Feb 27, 2013: read about a workaround to get a Volume License Agreement for just 30$. UPDATE Aug 16, 2013: you can get PowerPivot with Excel stand-alone, without the need of any subscription.

    This might disappoint those of you that are used to buying a single license that never expires, but there is a good reason to move to Office 365 for using BI features: in the upcoming months and years, you will automatically receive updates of Office before perpetual (non-subscription) customers, and Excel will increase the number of BI features available at a faster cadence than ever before (yes, they promised it!). If you attended MS conferences and/or watched some of the last keynotes of BI speeches, you might have already seen some interesting pviews (i.e. 3D mapping with GeoFlow for Excel), and probably more is coming.

    Again, it’s important to know that Office Standard 2013 does not include Business Intelligence features, so all the options available when you need fewer than five licences does not include PowerPivot and Power View. You have to get a subscription of Office 365 ProPlus in this case, and the only action you can to today is using the free pview of Office 365 ProPlus until the end of February, when such a subscription will be commercially available.

    I have seen some confusion in these first days of Office 2013 availability and for this reason I think it is important to clarify what is the right version of Excel you have to buy in order to use PowerPivot. I will update this blog post as soon as the Office 365 ProPlus will be commercially available.

    UPDATE Aug 16, 2013: please remember that now Excel 2013 stand-alone now includes Power Pivot! You no longer need a subscription!

    --- Bài cũ hơn ---

  • Pivot Table Là Gì? Hướng Dẫn Sử Dụng Thành Thạo Pivot Table Trong Excel
  • How To Create Pivot Tables In Excel 2022
  • Using Pivot Tables In Excel 2022
  • Microsoft Office: 9 Pivottable Enhancements In Excel 2022
  • How To Create A Pivot Table In Excel 2010
  • Power Pivot For Excel Tutorial: Top Use Cases And Examples

    --- Bài mới hơn ---

  • How To Refresh Pivot Table In Excel (Manually + Auto
  • How To Refresh Pivot Table When Data Changes In Excel?
  • Refresh Pivot Tables Automatically When Source Data Changes
  • Hướng Dẫn Cách Dùng Hàm Pi Trong Power Bi Dax
  • The Complete Guide To Power Query
  • Read the Spanish version of this article translated by Marisela Ordaz

    Executive Summary

    What Is Power Pivot?

    • Introduced to Excel 2010 and 2013 as an add-on, but now native to the application, Power Pivot is part of Microsoft’s business intelligence stack capable of (but not limited to) big data analytics work without specialty infrastructure or software.
    • According to Microsoft, “Power Pivot enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and further analyze the data so that you can make timely business decisions without requiring IT assistance.”
    • Power Pivot was created in direct response to the big data demands of contemporary business intelligence needs, which prior generations of Excel-given their 1,048,576 row limit or processing speed shortcomings-struggled to cope with.
    • Power Pivot is expssed by Microsoft using DAX (Data Analysis Expssions), which is a collection of functions, operators, and constants usable in a formula or expssion to calculate/return one or more values.

    What Are the Benefits to Power Pivot vs. Basic Excel?

    • Power Pivot lets you import and manipulate hundreds of millions of rows of data whereas Excel has a hard constraint of just over a million rows.
    • Power Pivot allows you to import data from multiple sources into one single source workbook without having to create multiple source sheets and deal with potential version control and transferability issues.
    • Power Pivot lets you manipulate the imported data, analyze it, and draw conclusions without slowing down your computer system, as is typical of Basic Excel.
    • Power Pivot lets you visualize and manipulate your big datasets with PivotCharts and Power BI, where basic Excel lacks these capabilities.

    How Can a Finance Expert or Excel Consultant Help Your Business?

    • By working alongside you as a thought partner to design, structure, build, and deliver a range of financial models, budgets, and big-data analyses/projects, all en route to decisions around captive projects, mergers and acquisitions, or strategic investments.
    • By creating customized models unique to your business, using Power Pivot and other specialty excel functions.
    • By also creating pfabricated, go-to templates that can be adapted uniquely by almost anyone across your organization, for almost any purpose, using Power Pivot and other specialty Excel functions.
    • By actualizing each of these and more, alongside the design, creation, and delivery of a polished and professional PowerPoint psentation, ahead of strategic decisions.

    Download the data set here to follow along with the tutorial.

    The Emperor’s New Clothes: Power Pivot Tutorial

    What Is Power Pivot and Why Is It Useful?

    Power Pivot is a feature of Microsoft Excel that was introduced as an add-in to Excel 2010 and 2013, and is now a native feature for Excel 2022 and 365. As Microsoft explains, Power Pivot for Excel “enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance.”

    The primary expssion language that Microsoft uses in Power Pivot is DAX (Data Analysis Expssions), although others can be used in specific situations. Again, as Microsoft explains, “DAX is a collection of functions, operators, and constants that can be used in a formula, or expssion, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.” Fortunately for those already familiar with Excel, DAX formulas will look familiar, since many of the formulas have a similar syntax (e.g., SUM, AVERAGE, TRUNC).

    For clarity, the key benefits of using Power Pivot vs. basic Excel can be summarized as the following:

    • It lets you import and manipulate hundreds of millions of rows of data where Excel has a hard constraint of just over a million rows.
    • It allows you to import data from multiple sources into one single source workbook without having to create multiple source sheets that suffer from version control and transferability issues.
    • It lets you manipulate the imported data, analyze it, and draw conclusions without slowing down your computer to a snail’s pace.
    • It lets you visualize the data with PivotCharts and Power BI.

    In the following sections, I’ll run through each of the above and show you how Power Pivot for Excel can be helpful.

    How to Use Power Pivot

    1) Importing Large Datasets

    As pviously alluded to, one of the major limitations of Excel pertains to working with extremely large datasets. Fortunately for us, Excel can now load well over the one-million row limit directly into Power Pivot.

    To demonstrate this, I generated a sample dataset of two years’ worth of sales for a sporting goods retailer with nine different product categories and four regions. The resulting dataset is two million rows.

    Using the Data tab on the ribbon, I created a New Query from the CSV file (see Creating a New Query below). This functionality used to be called PowerQuery, but as of Excel 2022 and 365, was more tightly integrated into the Data tab of Excel.

    From a blank workbook in Excel to loading all two million rows into Power Pivot, it took about one minute! Notice that I was able to perform some light data formatting by promoting the first row to become the column names. Over the past few years, the Power Query functionality has vastly improved from an Excel add-in to a tightly integrated part of the Data tab on the toolbar. Power Query can pivot, flatten, cleanse, and shape your data through its suite of options and its own language, M.

    2) Importing Data from Multiple Sources

    One of the other key benefits of Power Pivot for Excel is the ability to easily import data from multiple sources. Previously, many of us created multiple worksheets for our various data sources. Often, this process involved writing VBA code and copy/pasting from these disparate sources. Fortunately for us, though, Power Pivot allows you to import data from different data sources directly into Excel without having to run into the issues mentioned above.

    Using the Query function in Exhibit 1, we can pull from any of the following sources:

    Further, multiple data sources can be combined either in the Query function or in the Power Pivot window to integrate data. For example, you can pull production-cost data from an Excel workbook and actual sales results from SQL server through the Query into Power Pivot. From there, you can combine the two datasets by matching production-batch numbers to produce per-unit gross margins.

    3) Working with Large Datasets

    Measures

    Excel junkies will no doubt agree that PivotTables are both one of the most useful, and at the same time, one of the most frustrating tasks we perform. Frustrating particularly when it comes to working with larger data sets. Fortunately, Power Pivot for Excel allows us to easily and quickly create PivotTables when working with larger sets of data.

    In the image below, entitled Creating Measures, notice how the Power Pivot window is separated into two panes. The top pane has the data, and the bottom pane houses the measures. A measure is a calculation that is performed across the entire dataset. I have entered a measure by typing in the highlighted cell.

    Total Sales:=SUM('Accounting Data')

    From there, watch how quickly it is to create a familiar PivotTable on a large dataset.

    Dimension Tables

    As financial analysts using Excel, we become adept at using convoluted formulas to bend the technology to our will. We master VLOOKUP, SUMIF, and even the dreaded INDEX(MATCH()). However, by using Power Pivot, we can throw much of that out the window.

    To demonstrate this functionality, I created a small reference table in which I assigned each Category to a Type. By choosing “Add to Data Model,” this table is loaded into Power Pivot (see Adding a User-created Table to a Power Pivot Model above).

    I also created a date table to use with our dataset (see Creating a Date Table below). Power Pivot for Excel makes it easy to create a date table quickly in order to consolidate by months, quarters, and days of the week. The user can also create a more custom date table to analyze by weeks, fiscal years, or any organization-specific groupings.

    Calculated Columns

    Besides measures, there is another type of calculation: calculated columns. Excel users will be comfortable writing these formulas, as they are very similar to writing formulas in data tables. I have created a new calculated column below (see Creating a Calculated Column below) which sorts the Accounting Data table by Amount. Sales below $50 are labeled “Small,” and all others are labeled “Large.” Doesn’t the formula feel intuitive?

    Relationships

    We can then create a relationship between the Accounting Data table’s Category field and the Category table’s Category field using the Diagram View. Additionally, we can define a relationship between the Accounting Data table’s Sales Date field and the Calendar table’s Date field.

    Now, without any SUMIF or VLOOKUP functions needed, we can create a PivotTable that calculated Total Sales by year, and type, with a slicer for Transaction Size.

    Or, we can create a chart of Average Sales for each day of the week using the new Calendar table.

    While this chart looks simple, it is impssive that it took less than ten seconds to create a consolidation over two million rows of data, without adding a new column to the sales data.

    While being able to perform all these consolidated reporting scenarios, we can always still drill down into the inpidual line items. We retain our highly granular data.

    Advanced Functions

    Time Intelligence

    Often, when we examine financial results, we want to compare it to a comparable timeframe from the pvious year. Power Pivot has some built-in time intelligence functions.

    Same Period Last Year Sales:=CALCULATE()) YOY Sales Growth:=if(not(ISBLANK(/,/CALCULATE(/CALCULATE())

    Those measures can now be deployed in a new PivotTable:

    Notice how the calculations are performed at both the category and seasonal type level. I love how quickly and effortlessly these calculations are performed on such a large dataset. These are just a few examples of the elegance and sheer computational power of Power Pivot.

    Compssion

    Another benefit is that file sizes shrink. The original file size was 91MB, and now it is under 4MB. That is a compssion of 96% of the original file.

    How does this happen? Power Pivot uses the xVelocity engine to compss the data. In simple terms, the data is stored in columns rather than rows. This storage method allows the computer to compss duplicate values. In our example dataset, there are only four regions that are repeated over all two million rows. Power Pivot for Excel can more efficiently store this data. The result is that for data that have many repeating values, it costs much less to store this data.

    One thing to note is that I used whole-dollar amounts in this sample dataset. If I had included two decimal points to reflect cents, the compssion effect would lessen to a still-impssive 80% of the original file size.

    SSAS Tabular

    Power Pivot models can also be scalable to the entire enterprise. Let’s say you build a Power Pivot model that starts gaining many users in the organization, or the data grows to ten million rows, or both. At this point, you may not want thirty different users refreshing the model or making changes. The model can be seamlessly converted into SSAS Tabular. All the tables and relationships are retained, but now you can control the refresh frequency, assign roles (e.g., read-only, read and process) to various users, and deploy only a small Excel front-end which links into the Tabular model. The result is that your users could then access the deployed Tabular model with a small workbook, but not have access to the formulas and measures.

    4) Data Visualization and Analysis

    CUBE Formulas

    One of the constant requests of my clients is that I create reporting that conforms to a strictly defined layout. I have clients that request specific column widths, RGB color codes, and p-defined font names and sizes. Consider the following dashboard:

    How do we populate the sales numbers without generating PivotTables if all of our sales are housed with Power Pivot for Excel? Using CUBE formulas! We can write CUBE formulas within any Excel cell and it will perform the calculation using the Power Pivot model we have already constructed.

    For example, the following formula is typed in the cell under “2016 Total Sales:”

    =CUBEVALUE("ThisWorkbookDataModel","",".[2016]")

    The first part of the formula, highlighted in yellow, refers to the name of the Power Pivot model. In general, it is usually ThisWorkbookDataModel for newer versions of Power Pivot for Excel. The portion in green defines that we want to use the measure Total Sales. The part in blue instructs Excel to filter for only rows that have a Sales Date with a year equal to 2022.

    Behind the scenes, Power Pivot has constructed an Online Analytical Processing (OLAP) cube with the data, calculated columns, and measures. This design allows the Excel user to then access the data by fetching directly with the CUBE functions. Using CUBE formulas, I have been able to construct full financial statements that conform to pdefined layouts. This capability is one of the highlights of using Power Pivot for Excel for financial analysis.

    Power BI

    One great thing about Power BI is the Natural Language Q&A. To demonstrate, I uploaded the Power BI model onto my online Power BI account. From the website, I can ask questions and Power BI constructs the appropriate analysis as I type:

    This type of query ability enables the user to ask questions of the data model and interact with the data in an easier way than in Excel.

    Another benefit of Power BI is that the developers at Microsoft are constantly releasing updates to it. New features, many user-requested, are pushed out monthly. Best of all, it is a seamless transition from Power Pivot for Excel. So, the time you invested learning the DAX formulas can be deployed in Power BI! For the analyst who needs to share his analysis to many users on varying devices, Power BI may be worth exploring.

    Best Practices

    Once you get started, there are a few best practices that you should follow.

    The first is to thoughtfully decide what to import in the first place. Will you ever use the salesperson’s home address? Do I need to know my customer’s email address in the context of this workbook? If the goal is to aggregate the data into a dashboard, then some of the data that is available will not be necessary for those calculations. Spending time curating the data coming in will greatly alleviate issues and memory-usage later when your dataset expands.

    Another best practice is to remember that Power Pivot is not Excel. In Excel, we are accustomed to creating calculations by constantly expanding our worksheets to the right. Power Pivot for Excel most efficiently processes the data if we limit this desire for manifest destiny. Instead of continuously creating calculated columns to the right of your data, learn to write measures in the bottom pane. This habit will ensure smaller file sizes and quicker computations.

    Finally, I would suggest using plain-English names for measures. This one took me a long time to adopt. I spent the first few years making up names like SumExpPctTotal, but once other people began to use the same workbooks, I had a lot of explaining to do. Now, when I start a new workbook, I use measure names like Expense Line Item as Percent of Total Expenses. While the name is longer, it is much easier for someone else to use.

    Real-World Use Cases

    In this article I have psented only a handful of the ways in which Power Pivot for Excel allows you to take an important step beyond plain-vanilla Excel. I thought it would be useful to highlight some real-world use cases in which I’ve found Power Pivot for Excel is extremely useful.

    Here are some:

    • Analyze performance of a large portfolio of assets over varying time ranges: Since Power Pivot for Excel allows us to define measures that compare a time-period with a pvious one, we can quickly have quarter-over-quarter, year-over-year, and month-over-month performance all on a rolling basis by writing only a few measures.
    • Summarize accounting data using customized aggregation levels: By identifying each general-ledger line item by name, category, and financial statement, reports can quickly be created that include the appropriate line items.
    • Chains can identify same-store sales: Using a table that maps when stores come online, financial results can be compared on a same-store basis.
    • Pinpoint over- and under-performers in sales: PivotTables can be created that highlight the top five SKUs and bottom five SKUs by sales, gross margins, production timeframes, etc.
    • Retailers can define calendar tables that use a 4-4-5 configuration: Using a custom date table, a retailer can assign each day to a specific 4-4-5 month easily, then daily sales results can be rolled into the corresponding month.

    As financial analysts, we are required to perform complex calculations on ever-expanding datasets. Since Excel is already the default analytical tool, the Power Pivot learning curve is easy, and many of the functions mirror Excel’s native functions.

    With the use of CUBE functions, Power Pivot for Excel seamlessly blends into your existing Excel workbooks. The computational efficiency gain cannot be overlooked. Assuming a 20% faster processing speed, which is conservative, the financial analyst that spends six hours a day within Excel can save 300 hours a year!

    Additionally, we can now analyze datasets that are much larger than we could pviously with our traditional Excel. With models designed efficiently, we can easily have 10x the amount of data that we pviously were allowed in traditional Excel, while maintaining quick analytical agility. With the ability to convert the models from Power Pivot to SSAS Tabular, the amount of data that can be processed is 100-1,000 times what we can achieve in Excel.

    If you’re interested in trying Power Pivot for Excel out, below are some useful materials to get you started.

    Useful References and Guides

    Collie, R., & Singh, A. (2016). Power Pivot and Power BI: The Excel user’s guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016. United States: Holy Macro! Books.

    Ferrari, A., & Russo, M. (2015). The definitive guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI. United States: Microsoft Press, USA.

    --- Bài cũ hơn ---

  • How To Use Powerpivot In Excel: The Ultimate Guide
  • Hướng Dẫn Cách Phân Biệt Giữa Power Pivot Và Pivot Table
  • Power Pivot For Excel: What It Is And How To Use It
  • Hướng Dẫn 200 Phím Tắt Excel Trên Máy Tính Window Và Macbook
  • How To Use The Excel Pivot Table Field List
  • How To Use Powerpivot In Excel: The Ultimate Guide

    --- Bài mới hơn ---

  • Power Pivot For Excel Tutorial: Top Use Cases And Examples
  • How To Refresh Pivot Table In Excel (Manually + Auto
  • How To Refresh Pivot Table When Data Changes In Excel?
  • Refresh Pivot Tables Automatically When Source Data Changes
  • Hướng Dẫn Cách Dùng Hàm Pi Trong Power Bi Dax
  • Power Pivot is an add-in first introduced in Excel 2010 and now a staple part of the modern Excel. It has changed the way that we can work with and manipulate large volumes of data in Excel.

    In this article, we will not only answer the question of what is Power Pivot? But also why and how to use PowerPivot with real business use cases.

    Download your data files

    Follow along with the steps in the article by downloading these data files

    What is Power Pivot & why is it useful?

    Although an Excel worksheet can handle 1,048,576 rows of data. In reality, it can struggle as you get to 100,000 or even before that depending on what you have in your workbook.

    Power Pivot enables us to work with big data beyond the 1,048,576 limitation and still produce smaller, leaner and faster workbooks than a standard PivotTable.

    It does this by loading the data into the internal data model of Excel and not onto a worksheet. Relationships can then be created between the different tables of data. No more VLOOKUPs to pull data together into one big list.

    We can then create PivotTables based on this model to analyze multiple tables of data.

    You can also use a powerful formula language in Power Pivot called DAX. This stands for Data Analysis Expssions.

    The DAX language is vast and enables us to perform more complex calculations than we can do with a standard PivotTable.

    So what is Power Pivot? It is really a combination of using PivotTables and DAX calculations with the internal data model of Excel for analysis of big data.

    Check out this short video that explains why we need Power Pivot:

    A Power Pivot use case

    Let’s look at an example business use case to see where Power Pivot will help us and I’ll explain how to use PowerPivot in this case.

    Let’s imagine a scenario where we export sales data from our database. This includes a CSV file of all sales transactions for a specified time period.

    It also includes a CSV file with all our customers and their details, and one with all our product details.

    We would like to import these 3 files into an Excel workbook to analyze them and find the top 5 selling products, as well as which countries we received over £10 million.

    Previously we would have imported the files into three different sheets and then used VLOOKUPs to pull the data into one big list for use in a PivotTable.

    But with Power Pivot, we will import them directly into the data model for efficient storage. Then create relationships between the tables (instead of thousands of VLOOKUPs). And perform analysis with a PivotTable and DAX.

    How to get and install the Power Pivot add-in

    In Excel 2013, 2022 and 365 Power Pivot is included as part of the native Excel experience. It will just take a few seconds to install it from the COM add-ins the first time you want to use it.

    The Power Pivot tab will then be visible on the Ribbon.

    If you are using Excel 2010 you will need to download the Power Pivot Add-In from the Microsoft Site.

    How to import CSV files to the Data Model

    We will now walk through our use case scenario.

    You can download the files and follow along for some hands-on practice.

    Download your data files

    Follow along with the steps in the article by downloading these data files

    Firstly we need some data. This data could already be in Excel. But often if you are working with large data sets you are getting data from a database, a folder or multiple text/CSV files.

    The best way to bring this data into Excel is by using Power Query. Power Query is a tool built into Excel to make importing and transforming external data simple.

    Power Query is outside the scope of this article, but here is a quick example of getting our sales data from CSV files. I will start with the chúng tôi file.

    The Power Query Editor window will load. There are a lot of tools we can use here to transform the data.

    This is just a quick example to get the data into the model for Power Pivot. So we will just close and load the data.

    The Import Data window appears. Select Only Create Connection and check the box to Add this data to the Data Model.

    The data is then loaded into the model. So you will not see it on the worksheet, but you will see a Queries and Connections pane appear showing the number of rows loaded.

    The image below shows the chúng tôi file loaded. It contains 106,693 rows. That is a lot of rows, but you will see that it does not impact the performance of calculations in Power Pivot.

    Repeat the process for the other 2 CSV files. When finished the loaded queries will look like below.

    Viewing the Data Model in Power Pivot

    The Power Pivot for Excel window is displayed.

    The initial view you are taken to is called the Data View. The tables of data are shown on different tabs, similar to worksheets. This is, however, just a display and not how they are stored.

    Underneath the tabs is the Calculation Area. We will talk more about this shortly when we cover measures.

    This provides a better view of the model and is great for viewing the relationships between the tables which we will create.

    Create relationships between the tables

    With the tables loaded into the model, we will now create relationships between them. This will enable us to create PivotTables using the data from all three tables.

    The Diagram View is the easiest way to set this up. Let’s start by arranging the window more efficiently.

    Drag the Sales table under the Products and the Customers tables.

    The Sales table contains the transactional information and is referred to as “the data”, or “the fact table”.

    The Products table and the Customers table contain information on groups of objects that interact with the data and are referred to as “lookup”, or “dimension tables”.

    We will create two ‘one to many’ relationships. One between the Customers table and the Sales table, and another between the Products table and the Sales table.

    This is because a customer could make one or many sales with us. And the same for the products. A product could be sold once or many times.

    Repeat the step to create a relationship between the Product ID field in the Sales table and the ID field in the Products table.

    The image below shows the completed relationships. The filter direction of the data is displayed by an arrow, and a 1 and asterisk (*) symbols are also displayed to show the relationship type.

    Create a PivotTable from the Data Model

    With the Data Model set up, we can create a PivotTable.

    The PivotTable appears and in the field list you can see the three tables. We can now access the fields from each table and drag them to the areas of a PivotTable as normal.

    So what is Power Pivot? It is a PivotTable that uses data from the internal model.

    Now let’s create one of our use case examples. We will find the top 5 selling products.

    Drag the Product Name field from the Products table into the Rows area. Then drag the Total Sales field from the Sales table into the Values area.

    This will sum the total for each product in our data.

    We have the top 5 products in a PivotTable.

    Now when we dragged the Total Sales field into the Values area of our PivotTable, it created what is called an Implicit Measure.

    We can use a PivotTable from the Data Model in the same way as we may be used to doing. This is great at the beginning or if you’re just performing simple analysis. But it is better and more efficient to create measures and to use them in PivotTables.

    Using DAX to create Measures

    Let’s begin to have a look at the DAX language to perform calculations in Power Pivot.

    There are two types of DAX calculations – Calculated Columns and Measures.

    A Calculated Column is used to create additional columns in your data model. And these columns can then be used as labels in the rows, columns and Slicer areas of a PivotTable.

    It is encouraged to create these columns in the original data, or in Power Query instead of the model if possible. These columns can be really useful for a further breakdown of our data such as grouping dates into weekday and weekend labels.

    In this article, we are going to create the other type of DAX calculation – called a Measure. Measures are calculations that are dragged into the values area of a PivotTable such as Sum and Average.

    The DAX language is huge, going far beyond the standard Sum and Average. So creating these in the model provides far more power than within a standard PivotTable and Implicit Measures.

    Measures created with DAX can also be used multiple times and in multiple PivotTables (but calculated just once). This improves the processing speed. You can also assign a format to a Measure so you won’t need to format them every time they are used.

    We will create a Measure to sum the Total Sales field from the Sales table.

    The Measure window appears.

    1. Select the table from the list that you would like the new measure stored within. This measure will be stored in the Sales table.
    2. Enter a name for the measure. This measure is named Sum of Sales.
    3. You can enter a description for a measure. Especially if complex. Here it is omitted since the name serves that role as well.
    4. Enter the following formula into the box provided: =SUM(Sales[Total Sales])

    Using a Measure in a PivotTable

    With the measure created we can use it in our PivotTables for analysis.

    Using the PivotTable we created earlier in the tutorial, we can remove the Sum of Total sales implicit measure.

    Our new measure is shown in the list of table fields and can be dragged into the Values area as a replacement.

    We will now create our other use case of showing in which countries we received over £10 million and re-use the same measure.

    Insert a new PivotTable as before and drag the Country field from the Customers table into Rows, and the Sum of Sales measure from the Sales table into Values.

    The countries that meet the criteria are shown as below.

    Our measure has been used in both PivotTables to help us achieve both use cases. The DAX language is capable of far more and I encourage you to read further in that area.

    In this article, we have answered the question of what is Power Pivot and demonstrated two business use cases on how to use PowerPivot through the entire process.

    We imported the data into the model, created relationships and a measure, and then used them in PivotTables.

    Power Pivot is one of the best improvements to how we use Excel. It is an extremely powerful tool and this article is an introduction to what it is capable of. I encourage you to learn and develop your Power Pivot skills even further.

    Alan Murray

    Alan is a Microsoft Excel MVP, Excel trainer and consultant. Most days he can be found in a classroom spading his love and knowledge of Excel. When not in a classroom he is writing and teaching online through blogs, YouTube and podcasts. Alan lives in the UK, is a father of two and a keen runner.

    --- Bài cũ hơn ---

  • Hướng Dẫn Cách Phân Biệt Giữa Power Pivot Và Pivot Table
  • Power Pivot For Excel: What It Is And How To Use It
  • Hướng Dẫn 200 Phím Tắt Excel Trên Máy Tính Window Và Macbook
  • How To Use The Excel Pivot Table Field List
  • Hướng Dẫn Cách Ẩn Các Nút Và Các Nhãn Của Pivot Table Trong Excel
  • Bắt Đầu Vào Power Pivot Bổ Trợ Cho Excel

    --- Bài mới hơn ---

  • Cách Tạo 1 Macro Cập Nhật Thay Đổi Dữ Liệu Pivot Table Đơn Giản Và Hiệu Quả Trong Excel
  • Hướng Dẫn Tạo Biểu Đồ Dạng Thác Nước Quản Lý Chênh Lệch Chi Phí Dự Án Trong Excel 2022
  • Xây Dựng Công Thức Tính Báo Cáo Chi Phí Tiền Lương Theo Bộ Phận
  • Hướng Dẫn Cách Lập Công Thức Tính Thời Gian Hoàn Vốn Của Dự Án Trong Excel
  • Hàm Lượng Giác Và Toán Học Trong Excel
  • Power Pivot là bổ trợ mà bạn có thể dùng để thực hiện phân tích dữ liệu mạnh mẽ trong Excel. Bổ trợ được xây dựng vào một số phiên bản của Office, nhưng theo mặc định, nó không được bật.

    Để biết danh sách các phiên bản của Office, bao gồm Power Pivot, cũng như danh sách các phiên bản không, vui lòng xem: Power Pivot ở đâu?

    Đây là cách bạn bật Power Pivot trước khi sử dụng nó lần đầu tiên.

    1. Chọn hộp Microsoft Office Power Pivot , sau đó bấm OK. Nếu bạn có phiên bản khác của Power Pivot bổ trợ được cài đặt, các phiên bản cũng được liệt kê trong danh sách bổ trợ COM. Hãy đảm bảo chọn Power Pivot bổ trợ cho Excel.

    Ribbon bây giờ có tab Power Pivot.

    Mở cửa sổ Power Pivot

    1. Bấm Power Pivot.

      Đây là tab nơi bạn làm việc với Power Pivot Pivottable, trường được tính toán và chỉ số hiệu suất then chốt (KPI), và tạo nối kết bảng.

    2. Bấm Quản lý.

    Bây giờ bạn đang ở trong cửa sổ Power Pivot. Tại đây bạn có thể bấm Lấy Dữ liệu Ngoài để dùng Trình hướng dẫn Nhập Bảng để lọc dữ liệu khi bạn thêm dữ liệu vào tệp, tạo mối quan hệ giữa các bảng, làm giàu dữ liệu bằng các phép toán và biểu thức, ròi dùng dữ liệu này để tạo PivotTables và PivotCharts.

    Thông tin thêm về Power Pivot.

    Khắc phục sự cố: Power Pivot ribbon biến mất

    Trong trường hợp hiếm, ruy-băng Power Pivot sẽ biến mất từ menu nếu Excel sẽ quyết định rằng bổ trợ đang gây bất ổn sang Excel. Điều này có thể xảy ra nếu Excel đóng đột ngột trong khi cửa sổ Power Pivot đang mở. Để khôi phục Power Pivot menu, hãy làm như sau:

    Nếu các bước trước đó không khôi phục ribbonPower Pivot hoặc nếu ribbon biến mất khi bạn đóng và mở lại Excel, hãy thử làm như sau:

    1. Đóng Excel.

    2. Trong soạn thảo sổ đăng ký, bung rộng khóa đăng ký này:

    3. Bấm chuột phải vào PowerPivotExcelAddin, rồi bấm Xóa bỏ.

    4. Cuộn lại lên đầu Trình soạn thảo Sổ đăng ký.

    5. Bấm chuột phải vào PowerPivotExcelClientAddIn.NativeEntry.1 rồi bấm Xóa bỏ.

    6. Đóng Trình soạn thảo Sổ đăng ký.

    7. Bật bổ trợ này theo các bước nêu ở phần đầu của bài viết này.

    --- Bài cũ hơn ---

  • Hướng Dẫn Cách Để Làm Mới Một Pivot Table Trong Excel?
  • Pivot Table Cơ Bạn Trong Excel
  • Hướng Dẫn Cách Sử Dụng Pivot Table Nâng Cao Trong Excel
  • Hướng Dẫn Cơ Bản Về Pivot Table Trong Excel Để Tổng Hợp Dữ Liệu
  • Dùng Pivot Trong Excel Để Lập Báo Cáo (Bằng 5 Bước Đơn Giản)
  • Web hay
  • Links hay
  • Guest-posts
  • Push
  • Chủ đề top 10
  • Chủ đề top 20
  • Chủ đề top 30
  • Chủ đề top 40
  • Chủ đề top 50
  • Chủ đề top 60
  • Chủ đề top 70
  • Chủ đề top 80
  • Chủ đề top 90
  • Chủ đề top 100
  • Bài viết top 10
  • Bài viết top 20
  • Bài viết top 30
  • Bài viết top 40
  • Bài viết top 50
  • Bài viết top 60
  • Bài viết top 70
  • Bài viết top 80
  • Bài viết top 90
  • Bài viết top 100
  • Chủ đề top 10
  • Chủ đề top 20
  • Chủ đề top 30
  • Chủ đề top 40
  • Chủ đề top 50
  • Chủ đề top 60
  • Chủ đề top 70
  • Chủ đề top 80
  • Chủ đề top 90
  • Chủ đề top 100
  • Bài viết top 10
  • Bài viết top 20
  • Bài viết top 30
  • Bài viết top 40
  • Bài viết top 50
  • Bài viết top 60
  • Bài viết top 70
  • Bài viết top 80
  • Bài viết top 90
  • Bài viết top 100