- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using CountRows and Distinct
If you need to figure out the average revenue per store, it helps to know how many distinct stores make up the revenue number. A formula such as =COUNTROWS(distinct(demo[StoreID])) will do the trick.
Revenue Per Store is then calculated as =sumx(demo,demo[Revenue])/demo[NumberStores].
)
Note that measures appear in the field list with a calculator icon next to them.
)
Using the Value and Filter Functions
The other cool thing about measures is that they automatically respect all filters applied to a cell in a pivot table, but with a little magic, you can build a calculation that partially ignores the filters!
First, consider how many filters are applied to cell C15 in the following figure?
){kind=small}
Certainly, the three slicers are these filters:
- FY=2009
- DM=Laura
- Division=Belts
So, are there only three filters? Actually, there are five filters on that one cell. The other two filters are the following:
- Quarter=Q1
- Region=Illinois
By default, the formula for a measure does not have to mention FY, DM, Division, Quarter, or Region. All those filters will be applied to the calculation.
Sometimes, you might want to perform a calculation where the divisor ignores one of the filters. Perhaps you want to show sales of belts as a percentage of handbags.
For those of you familiar with SUMIF or SUMIFS in Excel, the CALCULATE function in DAX does a similar operation. You can CALCULATE(SUM(demo[Revenue],<filter1 to apply or override>,<filter2 to apply or override>,<filter3 to apply or override>).
In the following example, the formula in D14 calculates revenue for 2009, Laura, all quarters, Atlanta. It forces PowerPivot to ignore the slicer selection for division (currently belts) and instead forces the division to be handbags.
)
In a similar fashion, you could override a time dimension to force PowerPivot to use the previous year's data.
There are many examples in ehivh DAX Measures can save the day. I spent a long chapter in PowerPivot for the Data Analyst going through more examples.