Creating Calculated Fields | CLEARIFY

Calculated Fields (Measures) - The Strength of PowerPivot

Creating Calculated Fields - known as Measures - which filter amounts or quantities for a specific range of information, is one of the biggest advantages of using PowerPivot over regular pivot tables.

In this out-of-the-box PowerPivot example, there are two pre-built measures (Margin %, Sales Amount YTD) in use. QQube provides 22 pre-built measures in the Sales Analytic - and hundreds more in other Analytics located in the QQube Configuration Tool.

In a regular pivot table, you can't create an IF calculation, for example: If the date of my transaction is in this month, give the sales amount, else give me 0.00.  PowerPivot does, however, give you this ability - and more.

The Microsoft DAX Language for creating Measures

DAX (Data Analysis Expressions) is a "library of functions and operators that can be combined to build formulas and expressions in PowerPivot"  These are the same methods used in Power BI.

We find that a good portion of the additional fields needed by end users is based upon a simple function call CALCULATE.  It essentially is a filtering mechanism that will separate what specific data you want in a column - apart from the other data.  This gets around the limitation of a regular pivot table where a filter must be applied to the whole range of data.

Here is the breakdown of a typical CALCULATE Function:

The complete DAX reference is listed on Microsoft here.

Managing and Creating Measures in PowerPivot

From the Power Pivot tab click on the Measures Icon, and choose either New or Manage.

Choosing Manage Measures will bring up a list of examples included in QQube:

If you choose New or highlight a measure and click Edit, you invoke the Formula Editor Window.