Jul 25, 2016
A Formula and a Measure are both calculations, but in general, you write a Formula to apply it to a PowerPivot Table whereas you write a Measure to apply to a PowerPivot PivotTable. In particular however the difference is in the way the calculations are performed…
When you write a Formula you write it to create a calculated column in a PowerPivot table, for example you might have a PowerPivot table that stores widths and lengths of all the rooms in a building amongst its other data, and you may now need a new column in that table that should store the areas of these rooms. In this occasion you need a calculated column using a Formula. Although the values of this column are calculated dynamically for each row of the table, they are less likely to change because the values of the width and length are pretty static. The Formula calculates area for every row irrespective of whether they will be used in a PivotTable or not. This way you are creating the data in the table before using it in the PivotTable.
On the other hand, imagine you have created a PivotTable out of the above table that shows you the total sum of the room-areas for each floor called RoomAreaPerFloor. If you now want to have a column in the PivotTable that calculates the percentages of each RoomAreaPerFloor as compared to the total FloorArea then you should create a Measure. Like a Formula, the Measure is also calculated on the fly when the PivotTable is being built but the Measure is calculated for each row of the PivotTable and not each row of the underlying Table. Therefore it may use some or all of the rows of the underlying table for its calculations depending on the query context of the PivotTable. These selected rows are usually the ones that are filtered in by the PivotTable therefore as the PivotTable’s query context changes the Measure calculates new values… and of course, the PivotTable’s query context is likely to change because that’s the whole idea of PivotTable. It’s there to extract different information by applying different queries on it.
How do your Excel skills stack up?
Test NowNext up:
- PhotoShop in action - Part 1
- Stop whinging; it’s boring!
- Windows Store for Business
- Create with Adobe’s Capture Mobile app: Creating Brushes
- Quick Tuts: Excel 2010 - vLookups simplified
- Social tagging in SharePoint 2010
- Tailoring your document with Styles
- 4 guides to help you become a PowerPoint pro
- Easy customisations in Word 2013
- Quick Tuts: PowerPoint 2010 - Animating SmartArt Graphics
Previously
- PowerShell Basics Series – Manipulating strings
- How-to: Give access to a list only in SharePoint 2013
- Managing for excellence: An innovative approach to managing performance
- How-To series: Password protect sections in OneNote 2013
- SharePoint 2016 social features
- 5 posts to help you become a better communicator
- How to omit dates in an Excel chart axis
- PowerShell Basics Series – Measuring objects (Part 2)
- Create cascaded drop-downs in Microsoft Access forms
- Working with Styles - Part 2