Record and item level measures (Item base amount,Invoice amount etc.)

What is the difference between record and item level measures?

In all cubes where the records have some rows on the records (Invoices, Sales Orders, Quotations etc.) there are two levels of measures:

  1. Record level (Invoice level, Quotation level etc.)
  2. Item level

The record level measures usually contain total amounts like the invoice amount, total tax amount, total profit amount etc. Item level measures contain row/item level amounts like item price, profit, cost of the particular item row.

Only Item level measures will work with Item dimension and Item classification dimensions. For example you can't split Invoice amount per item, but you can do that with Item base amount which is an Item level measure

What is the difference between normal and base measures?

The general rule is simple:

  • On the record level the non-base measures will return the total value of the record including taxes
  • On the record level the base measures will return the total value of the record including taxes in base currency
  • On the item level both the non-base and base measures will return the value excluding taxes in the currency and base currency respectively

If the invoice is in base currency - both measures will return the same value

Example

Let's assume you have sold 1 item which costs 100 EUR or approximately 109 USD and the VAT is 21% and the invoice is in USD.

In Sales cube the measures will return these numbers:

MeasureAmountWhy
Invoice amount131.89 USD109 USD + 21% VAT (and other taxes if applicable)
Tax amount22.89 USDTotal tax amount in USD
Invoice base amount121 EUR100 EUR + 21% VAT
Tax base amount21 EURTotal tax amount in EUR
Item amount109 USDTotal value in currency ex taxes
Item base amount100 EURTotal value in base currency ex taxes

If you wish to have a measure that returns total value ex taxes on the record level you can create one as a simple calculated member that subtracts the Tax amount from the Invoice amount etc.