MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Power Pivot Portable Formulas


September 01, 2017 - by Bill Jelen

Power Pivot Portable Formulas

Once you define a new measure in Power Pivot, that formula can be used in subsequent formulas.

If you have the full version of Power Pivot, you can use the DAX formula language to create new calculated fields. From the Power Pivot tab in the ribbon, choose Insert Calculated Field.

Give the field a name, such as Variance. When you go to type the formula, type =[. As soon as you type the square bracket, Excel gives you a list of fields to choose from.

Note that you can also assign a numeric format to these calculated fields. Wouldn’t it be great if regular pivot tables brought the numeric formatting from the underlying data?

Calculated Field
Calculated Field

In the next calculation, the VariancePercent is reusing the Variance field that you just defined.

VariancePercent Field
VariancePercent Field

This is great – all of those fields in the pivot table:

Pivot Table
Pivot Table

But you don’t have to leave any of those fields in the pivot table. If your manager only cares about the variance percentage, you can remove all of the other numeric fields.

Customized Pivot Table
Customized Pivot Table

Note that the DAX in this bonus tip is barely scratching the surface of what is possible. If you want to explore Power Pivot, you need to get a copy of DAX Formulas for Power Pivot by Rob Collie and Avi Singh

DAX Formulas for PowerPivot »


Thanks to Rob Collie for teaching me this feature. Find Rob at www.PowerPivotPro.com

Watch Video

  • With the Power Pivot tab, you can build relationships faster using the diagram view
  • You can also insert Calculated Fields or Measures to do new calculations
  • They use the DAX formula language
  • They are very powerful
  • Think of them as Helper Cells - they provide intermediate formulas

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2017 PowerPivot formulas
  • everything in the book is going to be
  • podcast in here in July August September
  • go ahead and subscribe to the watch list
  • of the playlist top right hand corner
  • and welcome back to the MrExcel in
  • that cast now yesterday in episode 2016
  • I showed how we can just use the data
  • model to take this small budget table in
  • this large actuals table at these three
  • joiners and create a pivot table today I
  • want to show you what you get if you pay
  • the extra two dollars a month to get the
  • office 365 pro plus and have power pivot
  • power pivot so here's what we're going
  • to do we're going to take these tables
  • and i'm going to add them to the data
  • model the budget tables it's already
  • there i'm going to take the joiners add
  • that and then back here the region add
  • that and then the calendar I'll add that
  • to the data model and then finally the
  • actuals i'm going to add that to the
  • data model and i'm going to arrange
  • these so we have the budget on the left
  • the joiners in the middle and now to
  • create the relationships this was a lot
  • of clicks yesterday i'm going to go from
  • the budget table product the region
  • table to region and the region date down
  • to date alright see how much faster it
  • is to create these relationships here in
  • the diagram view in the powerpivot
  • window so that's one advantage just a
  • small advantage I mean you can still do
  • this with the data model but you know it
  • is one small benefit but here's the
  • other things that we can do once we get
  • our report build so we have our actuals
  • in our budget and remember we use the
  • the fields from the jointer tables to
  • build out the pivot table
  • so we now have feels called budget and
  • revenue boy it'd be really nice to show
  • of variance and the powerpivot tab lets
  • us do that whether it's called a
  • calculated item in 2010 in 2013 or
  • whether it's called a measure in twenty
  • ten or twenty sixteen we can create a
  • new measure and this measure will be
  • called variance and it's going to be
  • equal to the budget field minus the
  • actuals field and I can even format that
  • as currency with zero decimal places
  • this is cool click OK alright see so now
  • for each data point we have a budget we
  • have an actual and we have a variance
  • what it'd be nice to take the variance
  • divided by the budget to get a variance
  • percent so we'll insert a new measure
  • and this is going to be that variance
  • field so the logic that I use to create
  • the variance field is reusable divided
  • by the sum of budget and this is going
  • to be a number but specifically a
  • percentage with one decimal place all
  • right so now I have four fields but
  • here's the beautiful thing if all I want
  • to show is the variance percentage I
  • don't need to show the variance the
  • actuals or the budget I can remove those
  • fields and the logic still works alright
  • so those are let's call them helper
  • formulas that help us get to the the
  • variance percent I only need to keep the
  • variance percent everything else stays
  • in the data model is understood it's all
  • calculated behind the scenes and it
  • works beautifully and they continue to
  • work even if we change the shape of the
  • pivot table it keeps recalculating it's
  • a beautiful thing again thanks to rob
  • Kali and you should check out his book
  • for the whole compendium of things you
  • can do with these Dax formulas and you
  • can order Rob's book or my book online
  • right now use that I on the top right
  • hand corner alright episode recap so
  • Excel 2013 gave us the data model but
  • you have to pay more an extra two
  • dollars a month
  • to get to the powerpivot tab the office
  • 365 proplus will get you this you can
  • build relationships faster using the
  • diagram view but that's really nothing
  • new I mean you could build relationships
  • like I did yesterday just using the
  • dialog box but it's the calculated
  • fields or measures that is the real
  • benefit it uses the DAX formula language
  • very powerful they come like helper
  • cells they provide intermediate formulas
  • and you could build upon previous
  • formulas just really it's where the
  • power and power pivot comes from all
  • right way I want to thank you for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2017.xlsx

Title Photo: webandi / Pixabay


Bill Jelen is the author / co-author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.