If you want to know more, I highly recommend you check out this example, it includes short video tutorials.) (As incredibly useful as they are, I don’t have room here to describe the new functions COUNTROWS and DISTINCT, but it should be easy to get a feel – we are taking the distinct count of months and using then dividing our sales amount by it. And as an extra benefit, if you need to change the way is calculated later, you can do that in one place, and all of your other formulas that depend on it will also pick up the change! That savings becomes more pronounced when your original formula is more complex, of course. = SUM(Sales) / COUNTROWS(DISTINCT(Sales))īut using instead of rewriting the SUM saves me time. Which, given the definition of above, is *precisely* the same thing as rewriting the part as a SUM:
Portability example #2: Using 1 formula inside anotherįormulas being saved as new fields will come in handy in our next step, too, which is calculating “Dollars per Month.” I can use the formula I defined above as an input to my new formula! But as your formulas get more complex, it is a MONSTER time-saver. Reusing a PowerPivot formula in other pivots: You just click the check boxįor a formula as simple as, this may not be a huge deal.
The simplest demonstration of said benefit is this: You can now use your formula in other pivots, even on different worksheets!įor example, here’s another pivot showing broken out by DayOfWeek, and did NOT need to be rewritten, all I needed to do was click the check box: That behavior – the formula getting saved as its own new field (checkbox) provides a lot of benefit. = SUM(Sales) Our first example of portability can be used in other pivots! If you write a formula for sales, it becomes its own check boxįor extra clarity, I like to express my measure formulas as “ =. It yields the same results as above, but it ALSO adds a new check box to the field list: It’s a pretty simple formula:įor space considerations, I’m not going into the details of that formula, but it should be pretty easy to get the idea. I did NOT need to write a formula in order to get “Sum of Sales” in the pivot – I just checked the Sales check box, and it automatically handles some of the problems from above.īut I CAN write a formula for Sales if I want. No formulas needed so far… just 4 clicks and the Month/Year/Category problem from before is covered You check four check boxes, and here’s what you’ve got: Let’s start with a basic PivotTable that looks like one of our first steps in part 1. OK, now we’re going to replay part 1, but this time we are going to use PowerPivot. If you missed part 1, I highly recommend reading it first, because this won’t make much sense on its own. Former Excel team member Rob Collie is CTO of Pivotstream and writes the PowerPivotPro blog, describing his experiences adopting Excel PowerPivot.
0 kommentar(er)
