Purchases Data Model

Modified on Wed, 29 Jul at 1:12 AM

Model Summary

The QQube Purchases Data Model analyzes item-based purchasing activity and is used primarily for pricing and cost analysis.

Model Type	Item-based purchasing and vendor analysis
Transaction Coverage	Linked posting and non-posting transactions
Data Scope	Items only
Status Coverage	Open and paid purchasing activity
Available Fields	More than 1,000

Best Used For

Analyzing buying patterns, top purchases, discounts, and average vendor transactions.
Comparing item rates, average price paid per quantity, overall averages, and total purchased value.
Reviewing purchasing activity by job dates, job status, active jobs, or jobs with active estimates.
Analyzing purchases by job type, 52/53 tax year, or fiscal year.
Reviewing open Purchase Order quantities, received quantities, and remaining commitments.

Key Capabilities

Includes linked posting and non-posting transactions.
Separates original, received, and open Purchase Order quantities and amounts.
Includes purchasing paid and open amounts.
Includes printed and base units of measure.
Includes home and foreign currency values.

Cost and Payable Treatment

Landed costs are included in the cost calculation when a customer has been invoiced.
When enhanced receiving is enabled in QuickBooks, an Item Receipt that has not been converted to a Bill is considered a payable.

QuickBooks Data Limitations

Purchase Order and Vendor Bill lines are not directly linked.

QuickBooks does not provide a line-level connection between a specific Purchase Order line and a Vendor Bill line.

Quantity, Rate, and Amount Fields

Native fields come from QuickBooks purchase data. QQube field extensions add printed-unit values and additional QuickBooks-calculated COGS amounts.

Native Fields (21)

Quantity Fields	Rate Fields	Amount Fields
Line PO Original Quantity Line PO Received Quantity Line PO Open Quantity Line Purchasing Quantity	Line Rate Line Rate (Foreign) Line Rate Percent Line Exchange Rate	Line PO Original Amount Line PO Original Amount (Foreign) Line PO Original Amount (CAN) Line PO Received Amount Line PO Received Amount (Foreign) Line PO Received Amount (CAN) Line PO Open Amount Line PO Open Amount (Foreign) Line PO Open Amount (CAN) Line Purchasing Amount Line Purchasing Amount (Foreign) Line Purchasing Paid Amount Line Purchasing Open Amount

QQube Field Extensions (7)

Quantity Fields	Rate Fields	Amount Fields
Line PO Original Quantity [Printed] Line PO Received Quantity [Printed] Line PO Open Quantity [Printed] Line Purchasing Quantity [Printed]	Line Rate [Printed]	Line Additional QB Calculated COGS Amount Line Additional QB Calculated COGS Amount (Foreign)

Power BI and Excel Power Pivot Measures

The prepared Purchases examples include 180 visible DAX measures. Hidden measures are not included.

Measure Section	Visible Measures
Measures Without a Display Folder	5
.Base Measures	28
Calendar Pattern Measures	36
Open PO Measures	11
Purchasing Amount Measures	47
Purchasing Quantity Measures	47
Vendor Measures	6

Measures without a display folder

Two are purchase-discount measures. Three support the Item, Job, and Vendor bridge tables used in Power BI so each slicer shows only values associated with the selected company. Excel Power Pivot handles this filtering automatically.

Included Dimensions

Calendars

52/53 Tax Year	Due Date	Expected 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
Calendar Overview

Document

Document Attributes

Line Attributes

Geography

Lists

Account (Purchases)	Class	Company
Currency	Customer	Customer Rep
Employee	Item	Item Sales Tax Code
Job	Job Rep	Other Name
Payroll Item	Sales Rep	Ship Method
Source Name	Template	Terms
UofM	Vendor

Technical Details

Model Structure	One Purchases FACT table with related dimensions and relationships already included
Item Scope	Item-based purchase activity only
Status Coverage	Open and paid purchasing activity
DAX Availability	Power BI and Excel Power Pivot examples

Expected Result

Open the prepared example and analyze purchasing activity by item, vendor, job, rate, amount, quantity, or reporting period.

The FACT table, dimensions, relationships, fields, QQube extensions, and measures are already included.