Microsoft PowerPivot for Excel 2010 – Data Analysis Expressions (DAX)

Formula is the reason that we use Excel; some formula are simple, e.g. “=A1+B1”, other can be complicated, e.g. DDB(cost,salvage,life,period,factor). PowerPivot also has its own formula, Data Analysis Expressions (DAX).  DAX is built on top of Excel formula but it is designed to work with multiple tables of data. It also adds new functionality that allows you to create dynamic aggregations.

You will be glad to know that most DAX functions are the same as Excel functions.  However, DAX functions always refer to a column or a table, e.g. “=[TotalSales][TotalCost]”; where [TotalSales] and [TotalCost] are 2 columns in the same table.

The biggest enhancement is DAX provides time intelligence function, which lets you use selected ranges of time frames and compare results across parallel periods.

There are 2 main types of DAX functions:

Calculated columns

Calculated column is a formula that includes functions and values, e.g. “=[Price] * 0.7”.  It is similar to the formula in Excel.

Measures

Measures are used to create dynamic aggregations for use in PivotTables which are placed into the Values area of a PivotTable. It would be evaluated for each cell within the PivotTable.  Based on the column value and row value, each cell will be calculated differently

DAX measures can be defined implicitly and explicitly.

Implicit DAX Measures

When you drag a field from PowerPivot Field List to the Values section, PowerPivot would automatically create a measure for you, e.g Sum of TotalProfit.

image

  • Change the Field Setting by right click on the measure and select Edit Measure.

image

  • Change the Custom Name to Profit by Category or change how the field to be aggregated, e.g. Average.

image

Implicit Measure only allows you to select simple aggregation (SUM, COUNT, AVERAGE, MIN, or MAX).  If you want anything advanced, then you must create a measure explicitly.

Explicit DAX Measures

To create a DAX measure,

  • Click anywhere on the PivotTable.
  • Select the New Measure on the PowerPivot ribbon.

image

  • Define the Measure Settings.

image

  • Click the fx button to bring up the Insert Function dialogue where you can select what formula to be used in the measure.

image

  • You can refer to other measures that are already established, e.g. “= CALCULATE ([Profit by Category ], DimChannel [ChannelName]=”Store”)”
  • The second portion of the formula, “DimChannel [ChannelName]=”Store”)”, is known as filter in PowerPivot which would constraint how this measure to be evaluated in the PivotTable,

Time Intelligence Functions

No matter if you are doing financial, sales, or other performance analysis, time is always a critical dimension.  You may want to compare year-to-date, same period last year, or year over year growth.  PowerPivot is much more powerful than Excel in term of time series analysis.  DAX provides a comprehensive list of time intelligence functions.  Below is a short list of what you can use:

  • FIRSTDATE (Date_Column)
  • LASTDATE (Date_Column)
  • FIRSTNONBLANK (Date_Column, Expression)
  • LASTNONBLANK (Date_Column, Expression)
  • STARTOFMONTH (Date_Column)
  • STARTOFQUARTER (Date_Column)
  • STARTOFYEAR (Date_Column [,YE_Date])
  • ENDOFMONTH (Date_Column)
  • ENDOFQUARTER (Date_Column)
  • ENDOFYEAR (Date_Column [,YE_Date])
  • PREVIOUSDAY (Date_Column)
  • PREVIOUSMONTH (Date_Column)
  • PREVIOUSQUARTER (Date_Column)
  • PREVIOUSYEAR (Date_Column [,YE_Date])
  • NEXTDAY (Date_Column)
  • NEXTMONTH (Date_Column)
  • NEXTQUARTER (Date_Column)
  • NEXTYEAR (Date_Column [,YE_Date])
  • DATESMTD (Date_Column)
  • DATESQTD (Date_Column)
  • DATESYTD (Date_Column [,YE_Date])
  • SAMEPERIODLASTYEAR (Date_Column)
  • TOTALMTD (Expression, Date_Column [, SetFilter])
  • TOTALQTD (Expression, Date_Column [, SetFilter])
  • TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date])
  • OPENINGBALANCEMONTH (Expression, Date_Column [,SetFilter])
  • OPENINGBALANCEQUARTER Quarter (Expression, Date_Column [,SetFilter])
  • OPENINGBALANCEYEAR (Expression, Date_Column [,SetFilter] [,YE_Date])
  • CLOSINGBALANCEMONTH (Expression, Date_Column [,SetFilter])
  • CLOSINGBALANCEQUARTER (Expression, Date_Column [,SetFilter])
  • CLOSINGBALANCEYEAR (Expression, Date_Column [,SetFilter] [,YE_Date])

The time intelligence functions also allow fiscal years having a yearend date other than Dec 31.

I hope you enjoy my blogs on PowerPivot but I cannot tell you more than 1% of what PowerPivot can do.  You must experience it yourself to truly appreciate its full potential.  Download Excel 2010, PowerPivot and sample data now.  Once you are able to trial it for couple hour, you would agree with me that PowerPivot is the biggest excitement of Excel 2010.

 

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

8 Responses to Microsoft PowerPivot for Excel 2010 – Data Analysis Expressions (DAX)

  1. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Slicer | Technologies and your business

  2. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Chart | Technologies and your business

  3. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Table | Technologies and your business

  4. Pingback: Microsoft PowerPivot for Excel 2010 – Creating Relationships Among Data | Technologies and your business

  5. Pingback: Microsoft PowerPivot for Excel 2010 – Importing Data | Technologies and your business

  6. Pingback: Why Microsoft PowerPivot? | Technologies and your business

  7. Pingback: Excel 2010 – PowerPivot | Technologies and your business

  8. Pingback: Microsoft PowerPivot for Excel 2010 – Introduction | Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: