DAX Measures - Sales Data Model

Modified on Tue, 28 Jul at 2:06 PM

Model Summary

The QQube Sales Data Model is a ready-made model for analyzing the complete QuickBooks sales cycle. It combines posting and non-posting sales transactions with linked transactions, quantities, rates, amounts, costs, margins, payments, credits, and discounts.

QuickBooks transactions	Estimates, Sales Orders, Invoices, Credit Memos, Sales Receipts, and Statement Charges
Transaction coverage	Posting, non-posting, open, invoiced, applied, and linked sales activity
Core values	Quantities, rates, sales, COGS, profit margins, open amounts, applied amounts, payments, credits, and discounts
Units and currency	Base and printed units of measure, plus home and foreign currency values

Best Used For

Analyzing customer buying patterns, purchase frequency, sales trends, and customer value.
Comparing sales, COGS, and profit margins by customer, item, class, sales representative, geography, or other included dimensions.
Tracking sales orders through invoicing, open quantities, fulfillment, and linked sales transactions.
Evaluating pricing, rates, discounts available, discounts taken, payments, credits, and remaining open amounts.
Identifying top customers, top items, profitable products, cross-sell opportunities, and seasonal sales patterns.
Creating commission, customer profile, and multi-company sales analyses.

Key Capabilities

Combines Estimates, Sales Orders, Invoices, Credit Memos, Sales Receipts, and Statement Charges in one model.
Includes posting, non-posting, and linked sales transactions.
Provides actual COGS and profit margins for inventory and assembly items.
Separates original, invoiced, open, applied, and remaining sales values.
Includes payments, credits, credit memos, and discounts applied to sales documents.
Provides printed and base unit-of-measure quantities and rates.
Includes home and foreign currency values.
Includes the related document, account, list, and calendar dimensions in the ready-made examples.

QuickBooks Data Limitations

QQube can expose and extend the relationships available in QuickBooks, but it cannot create relationships that QuickBooks does not store.

QuickBooks does not directly link a customer invoice to the vendor bill that may have supplied the goods or services.
The cost of a specific purchase or bill line cannot be tied to a specific customer invoice line.
Receive Payments are applied to the sales transaction as a whole, not to individual sales lines.
QuickBooks does not retain historical versions of a Sales Order. The model reflects the current state available during synchronization.
Estimate change orders are stored as text on the original estimate rather than as separate structured transactions.
QuickBooks transaction links are not item-to-item links. A linked document does not identify which individual source line corresponds to each destination line.

Review all QuickBooks Desktop data availability limitations

Quantity, Rate, and Amount Fields

These are the primary Sales Values fields included in the model. Native fields come from QuickBooks transaction data. QQube field extensions add calculated, normalized, printed-unit, linked, or applied values for analysis.

Native Fields (24)

Quantity Fields	Rate and Percentage Fields	Amount Fields
Line Estimate Quantity Line SO Original Quantity Line SO Invoiced Quantity Line SO Open Quantity Line Sales Quantity	Line Rate Line Rate (Foreign) Line Rate Percent Line Exchange Rate	Line Estimate Cost Amount Line Estimate Cost Amount (Foreign) Line Estimate Markup Amount Line Estimate Markup Percent Line Estimate Income Amount Line Estimate Income Amount (Foreign) Line SO Original Amount Line Sales Amount Line Sales Amount (Foreign) Line COGS Amount Line COGS Amount (Foreign) Line Sales Profit Margin Amount Line Sales Profit Margin Amount (Foreign) Line Sales Applied Amount Line Sales Open Amount

QQube Field Extensions (16)

Quantity Fields	Rate Fields	Amount and Applied Fields
Line SO Original Quantity [Printed] Line SO Invoiced Quantity [Printed] Line SO Initial Invoiced Quantity Line SO Open Quantity [Printed] Line Sales Quantity [Printed]	Line Rate [Printed]	Line SO Invoiced [SO Rate] Amount Line SO Invoiced [Actual Rate] Amount Line SO Open [SO Rate] Amount Line SO Open [Actual Rate] Amount Line Credits Applied On Document Line Credit Memo Amount Applied to Document Line Payments Applied On Document Line Payments Applied To Document Line Discounts Applied On Document Line Discounts Applied To Document

Power BI and Excel Power Pivot Measures

The prepared Power BI and Excel Power Pivot Sales examples include 368 visible DAX measures. They are organized into the same eight display folders used in the model. Hidden supporting measures are not included in the measure reference.

Measure Folder	Visible Measures
.Base Measures	40
Calendar Pattern Measures	101
COGS Measures	47
Customer Measures	27
Open SO Measures	11
Profit Margin Measures	47
Sales Amount Measures	47
Sales Quantity Measures	48

Included Dimensions

The following dimensions are already included in the prepared Sales examples. Use these links to review their fields, source details, and model-specific behavior.

Document

Document Attributes	Line Attributes
Geography	Sales Linked Transactions

Account

Account (COGS)	Account (Sales)
Account (Summary)	Account (Cash Source)

List

Bin Location	Class	Company
Currency	Customer	Customer Rep
Customer Message	Customer Sales Tax Code	Item
Item Sales Tax Code	Job	Job Rep
Payment Method	Sales Rep	Sales Tax Item
Ship Method	Site	Template
Terms	UofM

Calendars

These calendar dimensions are already included where applicable in the Sales Data Model.

52/53 Tax Year	Billed Date	Due Date
Fiscal Year	Fully Paid Date	Service Date
Ship Date	Suggested Discount Date	Transaction Date
Job End Date	Job Projected End Date	Job Start Date
More Information About Calendar Dimensions

Technical Details

Model structure	One Sales FACT table with the related dimensions and relationships already included.
Prepared applications	Excel, Excel Power Pivot, Power BI, Access, Tableau, and Crystal Reports.
DAX availability	The visible DAX measures are included in the Power BI and Excel Power Pivot examples.
Starting point	Open the prepared Sales example from the QQube Configuration Tool and customize the analysis.

Expected Result

Open the prepared Sales example from the QQube Configuration Tool for Excel, Excel PowerPivot, Power BI, Access, Tableau, or Crystal Reports.

The Sales FACT table, dimensions, relationships, fields, QQube extensions, and application-specific calculations are already included. Power BI and Excel Power Pivot examples also include the visible DAX measures listed in this guide. Begin with the prepared example and customize the analysis. You do not need to build the data model from scratch.