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:
- Record level (Invoice level, Quotation level etc.)
- 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
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:
|Invoice amount||131.89 USD||109 USD + 21% VAT (and other taxes if applicable)|
|Tax amount||22.89 USD||Total tax amount in USD|
|Invoice base amount||121 EUR||100 EUR + 21% VAT|
|Tax base amount||21 EUR||Total tax amount in EUR|
|Item amount||109 USD||Total value in currency ex taxes|
|Item base amount||100 EUR||Total 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.