Additional Calculations and Measures

Modified on Sun, 26 Jul at 3:56 AM

Overview

To calculate an amount or quantity for a specific time period in a PivotTable, you must create a separate calculated measure.

Standard Excel PivotTables do not provide this capability. PowerPivot and Power BI use DAX, or Data Analysis Expressions, to create these calculations.

In the following example, the DAX measure Quantity Sold - Current Month appears beside the general Quantity Sold measure.

PowerPivot report comparing Quantity Sold with Quantity Sold - Current Month

QQube Provides the DAX Measures

DAX can be complex, even for advanced users. QQube includes more than 5,700 prepared DAX formulas.

The methodology is based on the published work of DAX experts Marco Russo and Alberto Ferrari.

These prepared measures provide efficient calculations without requiring users to build the complete DAX framework themselves.

Available Measure Types

QQube measures are organized by purpose. Some are available for direct use in reports. Others are hidden because they support calculations behind the scenes.

Base Measures

Visibility: Available in reports

Base Measures are prefixed with a period, such as .Quantity Sold.

They generally summarize raw QQube fields. They do not contain filters and can be used as the foundation for other measures in any data model.

QQube Base Measures in PowerPivot

Intermediate Measures

Visibility: Hidden

Intermediate Measures are internal calculations used by other measures.

They can determine values such as today's date or the latest date in a transactional data set. They are hidden because they are not intended for direct use in reports.

Hierarchy Measures

Visibility: Hidden

Hierarchy Measures are used in Financial Statement, Job Profit and Loss, Trial Balance, and related data models, including Profit and Loss Detail.

They handle ragged financial groupings and account-level hierarchies. They are designed for use by other measures and are hidden intentionally.

Calendar Pattern Measures

Visibility: Available in reports

Calendar Pattern Measures divide information into consistent time buckets:

Year.
Quarter.
Month.
Week.

They support cumulative reporting and make it easier to display comparable periods side by side. In PowerPivot, they also return proper grand totals.

QQube Calendar Pattern Measures in PowerPivot

Calendar Specific Measures

Visibility: Available in reports

Calendar Specific Measures calculate a narrow, defined period instead of repeating equal time buckets across a longer range.

Examples include:

Month-To-Date.
Year-To-Date.
Last Month.
Yesterday.

QQube Calendar Specific Measures in PowerPivot

Comparison Measures

Visibility: Available in reports

Comparison Measures calculate the relationship between two Calendar Specific Measures.

They can return:

The difference between two periods.
The percentage difference between two periods.

QQube Comparison Measures in PowerPivot

Single Value Measures

Visibility: Available in reports

Single Value Measures do not follow a repeating calendar pattern and are not divided into specific calendar periods.

An example is Days Out of Stock.

QQube Single Value Measures in PowerPivot

Expected Result

Use the prepared QQube DAX measures in PowerPivot or Power BI to calculate filtered periods, cumulative totals, period comparisons, hierarchy behavior, and individual business metrics without creating the complete DAX framework from scratch.