Additional Calculations and Measures

Overview

In order to calculate a sum of an amount or quantity for a particular time period in a pivot table, you must use create a calculation as a separate field.  Regular Pivot Tables do not give you the ability to do this.

PowerPivot (and Power BI) uses a specific Microsoft methodology called DAX (Data Analysis Expressions) to achieve this result.

In this example a DAX formula was used to create "Quantity Sold - Current Month" so that it could co-exist next to the "Quantity Sold" column.

QQube Does the Hard Part

The DAX language can be very complex, even for the most advanced user.  So QQube came to the rescue and added over 5,700 DAX formulas.   

They are based upon the work of the world-renowned renowned DAX experts, Marco Russo and his partner Alberto Ferrari; which means that our methodology is based upon public - and not proprietary - knowledge.

It also means that you have the most efficient and effective measures available - anywhere.

Available measure types

.Base Measures. These are measures that are prefixed with a period (".") and are generally a summation of the raw QQube fields.  There are no filters in these measures and are available for use for any data model.

Intermediate Measures. These are internal measures to assess today's date or last date in a list of transactional data and designed to be used in other measures.  They are hidden on purpose.

Hierarchy Measures. Present in financial statement, job profit and loss, and trial balance related data models (including Profit and Loss Detail) to address ragged hierarchies of financial groupings and account levels. They are also designed to be used in other measures and hidden on purpose.

Calendar Pattern Measures. These allow for easy graphing of Cumulative by Year, Quarter, Month, Week.  For Power Pivot they are great for seeing columns of like data side by side, such as year, or month and will give proper grand total columns.

Calendar Specific Measures. Whereas Calendar Pattern Measures (?) allow for equal buckets over a long period of time, Specific Calendar Measures speak to a specific and narrow period of time, e.g. Month-To-Date, Year-To-Date, Last Month, Yesterday, etc.

Comparison Measures. Subtracting one specific calendar measure from another, % difference, to make it easier for the end-user.

Single Value Measures. Specific measures which don't follow a calendar pattern, nor are segmented into specific calendar buckets. e.g. Days out of Stock.