Order: Item_Values_Lines

by

What this view is for

Order.item_values_lines provides the numeric values associated with customer orders.

If:

  • Order.core_details defines what the order is
  • Order.meta_codes explains how it is classified
  • Order.meta_dates shows when things happen

then item_values_lines answers how much has been ordered — in quantity, value and profit terms.

This is the view where contracted order value lives.

A note on “lines” in the CDM

In the Common Data Model (CDM), the term lines can be used as a version indicator.

For Orders, this means:

  • One or more numeric records per order
  • Values expressed at a meaningful contractual grain
  • A structure that aligns with Busopp and Invoice for reuse and consistency

Think of this view as value instances, not invoice or fulfilment lines.

Level of detail (grain)

  • Multiple rows per order
  • One row per value attribute instance

This view is intentionally long and thin.

It is not safe to count rows here — this view exists to be aggregated deliberately.

What you’ll find in this view

Each row represents a single numeric value with unit and currency context, including:

  • OBJECT_SEQ – the paired company + order identifier
  • META_TYPE – the category of value
  • ATTRIBUTE – the specific value meaning
  • A numeric value
  • Currency or unit context (base, transaction currency, quantity)

No business logic or aggregation is applied in this view.

Current value attributes

The following META_TYPE / ATTRIBUTE combinations are available:

META_TYPEATTRIBUTE
SALESALES_QTY
SALEUNIT_PRICE_NET
SALEUNIT_PRICE_BASE
SALEUNIT_PRICE_GROSS
AMOUNTNET_CURR
AMOUNTGROSS_CURR
AMOUNTNET_BASE
AMOUNTGROSS_BASE
AMOUNTPROFIT

The naming makes units and currency explicit, reducing ambiguity at the point of use.

How to join this view

Every row includes OBJECT_SEQ, consistent with all Order views.

Always join Order.item_values_lines to Order.core_details using OBJECT_SEQ.

This ensures values are anchored to the correct order before any aggregation occurs.

Aggregating order values (with care)

In most reports, this view is aggregated early into a working meta_values layer.

This is normal and encouraged — but it must be done carefully.

Be mindful of currency and units

When aggregating:

  • Never mix base and currency values unintentionally
  • Be explicit about whether you are using:
    • Net vs gross values
    • Base vs transaction currency
  • Quantities (SALES_QTY) should never be summed with monetary values
  • Unit prices should usually be:
    • Averaged (often weighted)
    • Or kept at line level

If numbers feel “off”, unit or currency mixing is usually the cause.

Creating a combined value attribute

As with codes and dates, combining META_TYPE and ATTRIBUTE is strongly recommended.

Example (Power Query):

= Table.AddColumn(
    Source,
    "VALUE_ATTRIBUTE",
    each [META_TYPE] & "_" & [ATTRIBUTE],
    type text
)

This makes intent explicit and simplifies shaping and pivoting.

Pivoting and shaping values

Once filtered and combined, pivoting allows you to create a wide, report-friendly values table with:

  • One row per order (OBJECT_SEQ)
  • One column per value attribute
  • Clear separation of quantities, prices and amounts

This often becomes the foundation for reusable order measures.

How this view is commonly used

Order.item_values_lines is typically used to:

  • Calculate total net and gross order value
  • Analyse order profitability
  • Support quantity-based analysis
  • Feed committed revenue and backlog reporting

It is rarely used raw — aggregation is expected.

Things to watch out for

  • Don’t aggregate the same value more than once
  • Don’t mix currencies or units unintentionally
  • Be explicit about net vs gross logic

A simple check: if a value appears both before and after aggregation, something needs revisiting.

Where this fits in a report build

A typical flow is:

  1. Start with Order.core_details
  2. Add meta_dates and meta_codes for context
  3. Join item_values_lines for quantities and values
  4. Aggregate into a working meta_values layer
  5. Build measures on top of the aggregated values

This keeps identity, timing, classification and value cleanly separated.

Key takeaway

Order.item_values_lines is where contracted order value and quantity live.

Respect the grain, be explicit about currency and units, aggregate once — and it becomes a robust foundation for committed revenue reporting.

Leave a Comment