The Pivot Tables rely on two primary technologies when rendering data. First, it relies on Multi Dimensional Expression (MDX). Second, it also relies on the Excel cube() function. Both of these technologies are designed to extract data from the PowerPivot SSAS cube.
You need to click on PivotTable option within Reports group of the Home Ribbon. There are other types of artifacts available from the same drop down including
- Chart and Table (Horizontal)
- Chart and Table (Vertical)
- Two Charts (Horizontal)
- Two Charts (Vertical)
- Four Charts
- Flattened PivotTable
You will be promoted to select wither New Worksheet or Existing Worksheet once you make your selection of a PivotTable.
PivotTable is created with list of tables and column as well as drop zone. It must be familiar to you if you used PivotTables in Excel. Let’s complicate this PivotTable with additional column that is not available at the source. In PowerPivot we call these types of columns, measure columns. These measure columns are created with the help of DAX formulas. Measures can also be called facts, metrics or dimensions. For example, product table has product name, product cost and number of products sold. We can create a measure for this table that will represent Sales Amount for each product.
Pic 1: DAX Measure Settings
This measure column becomes visible in our PivotTable upon creation and then it can be used within our PivotTable as one of the data points for further analysis. You can drag and drop this measure field from PowerPivot Field List. Other steps for creating a PivotTable are very similar to PivotTable creation in Excel such as dragging and dropping fields into corresponding axis and applying any filtering criteria to it.
There are settled things with measures when you try to slice data. Calculated fields rely on this filtering. However, measures use DAX keyword ALL and override current filtering being applied to a PivotTable.