On this page you will find the specifications regarding the Model: CRM and Fact Table: CRM MEASURES.
| Attribute | Description (where applicable) |
Aggregation of measures is based on SUM if not explicitly noted.
This section describes the basic measures in the cube. These are either measures defined in the core Information Source, in the model itself or in the ETL process. The columns have the following meaning:
| Measure | Calculated Y/N | Contains Time Intelligence Y/N | Definition/Note (where applicable) | Aggregation Type | Hidden Y/N | Tabular Expression |
| Alternative Opportunity Count | N | Y | Count of rows which contain a value for alternative opportunity | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Count Alternative Opportunity] ) |
| Annual Turnover | Annual turnover amount | N | SUM ( 'BUSINESS LEAD FACT'[Annual Revenue]) | |||
| Answered On Time Count | N | Y | Count of rows which is TRUE for answered on time value | Sum | N | SUM ( 'SALES QUOTATION LINE'[Count Answered On Time] ) |
| Answered On Time Count PY | N | Y | Count of rows which is TRUE for answered on time value | N | CALCULATE ( [Answered On Time Count], SAMEPERIODLASTYEAR ( 'DATE WANTED'[ID] ) ) | |
| Avg Ship Days Early | Y | Y | Divide | N | DIVIDE ( [Sum Ship Days Early], [Count Early Order Lines] ) | |
| Avg Ship Days Late | Y | Y | Divide | N | DIVIDE ( [Sum Ship Days Late], [Count Late Order Lines], 0 ) | |
| Avg Ship Days Off Time | Y | Y | Divide | N | DIVIDE ( [Sum Ship Days Off Time], [Count Early Order Lines] + [Count Late Order Lines] ) | |
| Base Opportunity Value Total | N | Y | Total opportunity value in base currency | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Opportunity Total Value/Base] ) |
| CO Created Quote Lines Count | N | Y | Count of rows which contain a value for customer order | Sum | N | SUM ( 'SALES QUOTATION LINE'[Count CO Created Quote Lines] ) |
| Cancellation Quote Lines Count | N | Y | Count of rows which is TRUE for cancelled status | Sum | N | SUM ( 'SALES QUOTATION LINE'[Count Cancelled Quote Lines] ) |
| Char Price Calculated | Y | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Calculated Char Price] ) | |
| Complete Order Lines % | Y | Y | Divide | N | DIVIDE ( ( [Count CO Line] - [Count Incomplete Order Line] ), [Count CO Line] ) | |
| Cost | N | N | Sum of the amount | Sum | N | SUM ( 'SALES QUOTATION LINE'[Costs] ) |
| Cost Value Total | N | Y | Sum of the amount | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Opportunity Total Cost Value] ) |
| Count Activity | N | Y | Count of rows which contain a value for business activity | CountRows | N | COUNTROWS ( 'BUSINESS ACTIVITY FACT' ) |
| Count Agreement Order Lines | N | Y | Count of rows which contain a value for agreement | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_AGREEMENT_ORDER] ) |
| Count Business Activity | Count of rows which contain a value for business activity | N | SUM('BUSINESS ACTIVITY REFERENCE'[Count Business Activity]) | |||
| Count Business Oppurntunity Conn | N | Y | Count of rows which contain a value for business opportunity | Sum | N | SUM ( 'BUSINESS ACTIVITY FACT'[Count of Activity BO Connected] ) |
| Count CO Credit | N | Y | Count of rows which are set as credit | CountRows | N | COUNTROWS ( 'CUSTOMER INVOICE' ) |
| Count CO Line | N | Y | Count of rows which contain a value for customer order line | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_ORDER_LINE] ) |
| Count Calls | Count of rows where business activity type is call | N | SUM('BUSINESS ACTIVITY FACT'[Count of Activity Calls]) | |||
| Count Campaign Order Lines | N | Y | Count of rows which contain a value for campaign order line | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_CAMPAIGN_ORDER] ) |
| Count Campaign Response | Count of rows which contain a value for campaign response | N | COUNTROWS ( 'MARKETING CAMPAIGN RESPONSE FACT' ) | |||
| Count Chats | Count of rows where business activity type is chat | N | SUM('BUSINESS ACTIVITY FACT'[Count of Activity Chats]) | |||
| Count Complete Order Line | N | Y | Count of rows which is TRUE for completed status | N | [Count CO Line] - [Count Incomplete Order Line] | |
| Count Completed | N | Y | Count of rows which is TRUE for completed status | Sum | N | SUM ( 'BUSINESS ACTIVITY FACT'[Count of Activity Completed] ) |
| Count Completed Campaign Actions | Count of rows where status is completed | N | SUM( 'MARKETING CAMPAIGN ACTION FACT'[COUNT_COMPLETED] ) | |||
| Count Completed Campaigns | Count of rows where status is completed | N | SUM( 'MARKETING CAMPAIGN FACT'[COUNT_COMPLETED] ) | |||
| Count Converted Leads | Count of rows which is TRUE for converted leads | N | SUM ( 'BUSINESS LEAD FACT'[COUNT_CONVERTED] ) | |||
| Count Cust Inv | N | N | Count of rows which contain a value for customer invoice | CountRows | N | COUNTROWS ( 'CUSTOMER INVOICE' ) |
| Count Delivered Not Invoiced | N | Y | Count of rows which is TRUE for delivered as not invoiced status | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_DELIVERED_NOT_INVOICED] ) |
| Count Delivered Order Line | N | Y | Count of rows which contain a value for delivered order line | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_DELIVERED_ORDER_LINE] ) |
| Count Delivered as Planned | N | Y | Count of rows which is TRUE for delivered as planned status | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_DELIVERED_AS_PLANNED] ) |
| Count Delivered as Promised | N | Y | Count of rows which is TRUE for delivered as promised status | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_DELIVERED_AS_PROMISED] ) |
| Count Delivered as Requested | N | Y | Count of rows which is TRUE for delivered as requested status | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_DELIVERED_AS_REQUESTED] ) |
| Count E-Mails | Count of rows where business activity type is e-mail | N | SUM('BUSINESS ACTIVITY FACT'[Count of Activity Emails]) | |||
| Count Early Order Lines | N | Y | Count of rows which contain a value for early order line | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_EARLY_ORDER_LINE] ) |
| Count In Progress | N | Y | Count of rows where status is in progress | Sum | N | SUM ( 'BUSINESS ACTIVITY FACT'[Count of Activity in Progress]) |
| Count In Progress Campaign Actions | Count of rows where status is in progress | N | SUM ( 'MARKETING CAMPAIGN ACTION FACT'[COUNT_IN_PROGRESS] ) | |||
| Count In Progress Campaigns | Count of rows where status is in progress | N | SUM( 'MARKETING CAMPAIGN FACT'[COUNT_IN_PROGRESS] ) | |||
| Count In Progress Leads | Count of rows where status is in progress | N | SUM ( 'BUSINESS LEAD FACT'[COUNT_INPROGRESS] ) | |||
| Count Incomplete Order Line | N | Y | Count of rows where status is incomplete | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_INCOMPLETE_ORDER_LINE] ) |
| Count Late Order Lines | N | Y | Count of rows which contain a value for late order line | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_LATE_ORDER_LINE] ) |
| Count Leads | Count of rows which contain a value for lead | N | SUM ( 'BUSINESS LEAD FACT'[LEAD_COUNT] ) | |||
| Count Leads with existing Account | Count of rows which contain a value for existing account | N | SUM ( 'BUSINESS LEAD FACT'[COUNT_ON_EXISTING_ACC] ) | |||
| Count Marketing Campaign actions | Count of rows which contain a value for action | N | COUNTROWS ( 'MARKETING CAMPAIGN ACTION FACT' ) | |||
| Count Marketing Campaigns | Count of rows which contain a value for marketing campaign | N | COUNTROWS ( 'MARKETING CAMPAIGN FACT' ) | |||
| Count Meetings | Count of rows where business activity type is meeting | N | SUM('BUSINESS ACTIVITY FACT'[Count of Activity Meetings]) | |||
| Count Not Started | N | Y | Count of rows where status is not started | Sum | N | SUM ( 'BUSINESS ACTIVITY FACT'[Count of Activity Not Started]) |
| Count Not Started Campaign Actions | Count of rows where status is not started | N | SUM ( 'MARKETING CAMPAIGN ACTION FACT'[COUNT_NOT_STARTED] ) | |||
| Count Not Started Campaigns | Count of rows where status is not started | N | SUM( 'MARKETING CAMPAIGN FACT'[COUNT_NOT_STARTED] ) | |||
| Count On Time Order Line | N | Y | Count of rows which are set as on time order line | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_ON_TIME_ORDER_LINE] ) |
| Count Parked Leads | Count of rows where status is parked | N | SUM ( 'BUSINESS LEAD FACT'[COUNT_PARKED] ) | |||
| Count Perfect Order Line | N | Y | Count of rows which are set as perfect order line | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_PERFECT_ORDER_LINE] ) |
| Count PriceList Order Lines | N | Y | Count of rows which contain a value for price list | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_PRICELIST_ORDER] ) |
| Count RMA Line | N | Y | Count of rows which contain a value for RMA line | Sum | N | SUM ( 'RETURN MATERIAL LINE'[COUNT_ORDER_LINE] ) |
| Count Rejected Leads | Count of rows where status is not rejected | N | SUM ( 'BUSINESS LEAD FACT'[COUNT_REJECTED] ) | |||
| Count Returns to Inventory RMA Line | N | Y | Count of rows which contain a value for returns to inventory RMA line | Sum | N | SUM ( 'RETURN MATERIAL LINE'[COUNT_RETURNS_TO_INVENTORY] ) |
| Count Returns to Scrap RMA Line | N | Y | Count of rows which contain a value for returns to scrap RMA line | Sum | N | SUM ( 'RETURN MATERIAL LINE'[COUNT_RETURNS_TO_SCRAP] ) |
| Count SalesPart Order Lines | N | Y | Count of rows which contain a value for sales part | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[COUNT_BASEPRICE_ORDER] ) |
| Count Tasks | Count of rows where business activity type is task | N | SUM('BUSINESS ACTIVITY FACT'[Count of Activity Tasks]) | |||
| Count Unqualified Leads | Count of rows where status is not unqualified | N | SUM ( 'BUSINESS LEAD FACT'[COUNT_UNQUALIFIED] ) | |||
| Current Opportunity Value Total | N | Y | Total opportunity value | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Opportunity Total Value/Curr] ) |
| Deduction Amount Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[DEDUCTION_AMOUNT] ) |
| Disc Amount Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[DISC_AMOUNT] ) |
| Discount Amount | N | Y | Sum of the discount amount | Sum | N | SUM ( 'BUSINESS OPP LINE'[Discount] ) |
| Discount Total | N | Y | Sum of the amount | Sum | N | SUM ( 'SALES QUOTATION LINE'[Total Discount] ) |
| Discount Value | N | Y | Sum of the amount | Sum | N | SUM ( 'SALES QUOTATION LINE'[Discount Amount] ) |
| Due By Quantity | N | Y | Sum | N | SUM ( 'SALES QUOTATION LINE'[Quantity Due Buy] ) | |
| Due Revised Quantity | N | Y | Sum | N | SUM ( 'SALES QUOTATION LINE'[Quantity Due Revised] ) | |
| Duration | Duration in minuets of the business activity | N | SUM('BUSINESS ACTIVITY FACT'[Business Activity Duration]) | |||
| Early Order Lines % | Y | Y | Divide | N | DIVIDE ( [Count Early Order Lines], [Count CO Line], BLANK () ) | |
| Estimated Opportunity Value Base | N | Y | Sum of the amount | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Opportunity Estimated Value/Base] ) |
| Gross Amount Base | N | Y | Base Currency calculation | Sum | N | SUM ( 'SALES QUOTATION LINE'[Amount Base Gross] ) |
| Gross Amount Current | N | Y | Sum of the amount | Sum | N | SUM ( 'SALES QUOTATION LINE'[Amount Current Gross] ) |
| Gross Amount Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[GROSS_AMOUNT] ) |
| Gross Amount DOM Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[GROSS_AMOUNT_DOM] ) |
| Incomplete Order Lines % | Y | Y | Divide | N | DIVIDE ( [Count Incomplete Order Line], [Count CO Line] ) | |
| Inv Qty Received RMA Line | N | Y | Sum | N | SUM ( 'RETURN MATERIAL LINE'[INV_QTY_RECEIVED] ) | |
| Inv Qty Scrapped RMA Line | N | Y | Sum | N | SUM ( 'RETURN MATERIAL LINE'[INV_QTY_SCRAPPED] ) | |
| Inv Qty To Return RMA Line | N | Y | Sum | N | SUM ( 'RETURN MATERIAL LINE'[INV_QTY_TO_RETURN] ) | |
| Late Order Lines % | Y | Y | Divide | N | DIVIDE ( [Count Late Order Lines], [Count CO Line], BLANK () ) | |
| Lead Time Answered | N | Y | Sum | N | SUM ( 'SALES QUOTATION LINE'[Lead Time to Answered] ) | |
| Lead Time Delivery | N | Y | Sum | N | SUM ( 'SALES QUOTATION LINE'[Delivery Lead Time] ) | |
| Lost Quote Lines Count | N | Y | Count of rows where status is lost | Sum | N | SUM ( 'SALES QUOTATION LINE'[Count Lost Quote Lines] ) |
| Main Opportunity Count | N | Y | Count of rows which contain a value for main opportunity | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Count Main Opportunity] ) |
| Marketing Campaign Action Cost | Cost set for marketing campaign action | N | SUM ( 'MARKETING CAMPAIGN ACTION FACT'[Marketing Campaign Action Cost] ) | |||
| Net Amount Base | N | Y | Base Currency calculation | Sum | N | SUM ( 'SALES QUOTATION LINE'[Amount Base Net] ) |
| Net Amount CO Credit RC | Y | N | Reporting Currency calculation | N | SUMX ( 'CUSTOMER INVOICE', DIVIDE ( [REP_CURR_NET_AMOUNT], [Currency Rate] ) ) | |
| Net Amount CO Line RC | Y | Y | Reporting Currency calculation | N | SUMX ( 'CUSTOMER ORDER LINE', DIVIDE ( 'CUSTOMER ORDER LINE'[REP_CURR_NET_AMOUNT], [Currency Rate] ) ) | |
| Net Amount CO Line RC 1 | Y | N | Reporting Currency calculation | N | SUMX ( 'CUSTOMER ORDER LINE', DIVIDE ( 'CUSTOMER ORDER LINE'[REP_CURR_NET_AMOUNT], [Currency Rate] ) ) | |
| Net Amount Current | N | Y | Sum of the amount | Sum | N | SUM ( 'SALES QUOTATION LINE'[Amount Current Net] ) |
| Net Amount Cust Inv RC | N | N | Reporting Currency calculation | N | SUMX ( 'CUSTOMER INVOICE', DIVIDE ( [REP_CURR_NET_AMOUNT], [Currency Rate] ) ) | |
| Net Amount DOM Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[NET_AMOUNT_DOM] ) |
| Net Amount RMA Line RC | N | N | Reporting Currency calculation | N | SUMX ( 'RETURN MATERIAL LINE', DIVIDE ( [REP_CURR_NET_AMOUNT], [Currency Rate] ) ) | |
| Net Amount in Base Curr CO Credit | N | Y | Base Currency calculation | Sum | N | SUM ( 'CUSTOMER INVOICE'[NET_AMOUNT] ) |
| Net Amount in Base Curr CO Line | N | Y | Base Currency calculation | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[NET_AMOUNT_BASE] ) |
| Net Amount in Base Curr Cust Inv | N | N | Base Currency calculation | Sum | N | SUM ( 'CUSTOMER INVOICE'[NET_AMOUNT] ) |
| Net Amount in Base Curr RMA Line | N | Y | Base Currency calculation | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[NET_AMOUNT_BASE] ) |
| Net Amount in Order Curr | N | N | Sum of the amount | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[NET_AMOUNT_CURR] ) |
| Net Amount in Order Curr RMA Line | N | Y | Sum of the amount | Sum | N | SUM ( 'RETURN MATERIAL LINE'[NET_AMOUNT_CURR] ) |
| Normal Opportunity Count | N | Y | Count of rows which contain a value for opportunity | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Count Normal Opportunity] ) |
| Not Invoiced Amount | Y | N | Sum of the amount | N | IF ( [Qty Sales CO Line] <> 0, ( [Qty Sales CO Line] - [Qty Invoiced CO Line] ) * ( DIVIDE ( [Net Amount in Base Curr CO Line], [Qty Sales CO Line] ) ), 0 ) | |
| Not Invoiced Base Curr CO Line | N | N | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[NET_AMOUNT_NOT_INVOICED] ) | |
| NotInvoiced Base Curr CO Line | Y | N | N | ( [Qty Sales CO Line] - [Qty Invoiced CO Line] ) * DIVIDE ( [Net Amount in Base Curr CO Line], [Qty Sales CO Line] ) | ||
| NotInvoiced CO Line RC | Y | N | N | ( [Qty Sales CO Line] - [Qty Invoiced CO Line] ) * DIVIDE ( [Net Amount CO Line RC], [Qty Sales CO Line] ) | ||
| On time Order Lines % | Y | Y | Divide | N | DIVIDE ( [Count On time Order Line], [Count CO Line] ) | |
| Open Amount Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[OPEN_AMOUNT] ) |
| Open Amount DOM Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[OPEN_AMOUNT_DOM] ) |
| Opportunity Base Value Not Quoted | N | Y | The opportunity value that is not quoted, expressed in the company currency. | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Not Quoted Opportunity Value/Base] ) |
| Opportunity Base Value Quoted | N | Y | The opportunity value that is quoted, expressed in the company currency. | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Quoted Opportunity Value/Base]) |
| Opportunity Base Value Won | N | Y | The opportunity value that is won, expressed in the company currency. | N | SUM ( 'BUSINESS OPPORTUNITY'[Won Opportunity Value/Base] ) | |
| Opportunity Base Value Won PY | N | Y | The opportunity value that is won, expressed in the company currency. | N | CALCULATE ( [Opportunity Base Value Won], SAMEPERIODLASTYEAR ( 'DATE START'[ID] ) ) | |
| Opportunity Base Value Won R12 P | N | Y | The opportunity value that is won, expressed in the company currency. | N | CALCULATE ( [Opportunity Base Value Won R12], SAMEPERIODLASTYEAR ( 'DATE START'[ID] ) ) | |
| Opportunity Current Value Note Quoted | N | Y | The opportunity value that is not quoted, expressed in the opportunity currency. | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Not Quoted Opportunity Value/Curr] ) |
| Opportunity Current Value Quoted | N | Y | The opportunity value that is quoted, expressed in the opportunity currency. | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Quoted Opportunity Value/Curr]) |
| Opportunity Current Value Won | N | Y | The opportunity value that is won, expressed in the opportunity currency. | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Won Opportunity Value/Curr] ) |
| Opportunity Snapshot Count Confirmed | Count of rows where status is confirmed | N | COUNT ( 'BUSINESS OPP SNAPSHOT'[COUNT_CONFIRMED_OPPS] ) | |||
| Opportunity Snapshot Count In-Progress | Count of rows where status is in progress | N | COUNT ( 'BUSINESS OPP SNAPSHOT'[COUNT_INPROGRESS_OPPS] ) | |||
| Opportunity Snapshot Count On Alternative | Count of rows which contain a value for alternative opportunity | N | COUNT ( 'BUSINESS OPP SNAPSHOT'[COUNT_ON_ALTERNATE_OPP] ) | |||
| Opportunity Snapshot Count On Hold | Count of rows where status is on hold | N | COUNT ( 'BUSINESS OPP SNAPSHOT'[COUNT_ONHOLD_OPPS] ) | |||
| Opportunity Snapshot Count On Main | Count of rows which contain a value for main opportunity | N | COUNT ( 'BUSINESS OPP SNAPSHOT'[COUNT_ON_MAIN_OPP] ) | |||
| Opportunity Snapshot Count On Normal | Count of rows which contain a value for opportunity | N | COUNT ( 'BUSINESS OPP SNAPSHOT'[COUNT_ON_NORMAL_OPP] ) | |||
| Opportunity Snapshot Count Unconfirmed | Count of rows where status is unconfirmed | N | COUNT ( 'BUSINESS OPP SNAPSHOT'[COUNT_UNCONFIRMED_OPPS] ) | |||
| Opportunity Snapshot Current Value | Sum of the amount | N | SUM ( 'BUSINESS OPP SNAPSHOT'[Base Current Opportunity Value] ) | |||
| Opportunity Snapshot Value Estimated | Sum of the amount | N | SUM ( 'BUSINESS OPP SNAPSHOT'[Base Estimated Opportunity Value] ) | |||
| Opportunity Value Estimated | N | Y | Sum of the amount | Sum | N | SUM ( 'BUSINESS OPPORTUNITY'[Opportunity Estimated Value]) |
| Perfect Order Lines % | Y | Y | Divide | N | DIVIDE ( [Count Perfect Order Line], [Count CO Line] ) | |
| Planned Quote Lines Count | N | Y | Count of rows where status is planned | Sum | N | SUM ( 'SALES QUOTATION LINE'[Count Planned Quote Lines] ) |
| Price Char | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Char Price] ) | |
| Price Part | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Part Price] ) | |
| Profit | Y | N | N | [Net Amount in Base Curr CO Line] - [Total Cost in Base Curr CO Line] | ||
| Profit Margin % | Y | N | Divide | N | DIVIDE ( [Profit Margin in Base Curr], [Net Amount in Base Curr], BLANK () ) | |
| Profit Margin Goal | N | N | N | 0.1 | ||
| Profit Margin in Base Curr | N | N | N | [Net Amount in Base Curr] - [Total Cost in Base Curr] | ||
| Qty | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Quantity] ) | |
| Qty Assigned CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[QTY_ASSIGNED] ) | |
| Qty Invoiced CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[QTY_INVOICED] ) | |
| Qty Not Invoice CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[QTY_NOT_INVOICED] ) | |
| Qty Picked CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[QTY_PICKED] ) | |
| Qty Received RMA Line | N | Y | Sum | N | SUM ( 'RETURN MATERIAL LINE'[Total Received Qty] ) | |
| Qty Returned CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[QTY_RETURNED] ) | |
| Qty Returned Inv RMA Line | N | Y | Sum | N | SUM ( 'RETURN MATERIAL LINE'[INV_QTY_RETURNED] ) | |
| Qty Sales CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[SALES_QTY] ) | |
| Qty Shipdiff CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[QTY_SHIPDIFF] ) | |
| Qty Shipped CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[QTY_SHIPPED] ) | |
| Qty Short CO Line | N | N | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[QTY_SHORT] ) | |
| Qty To Return RMA Line | N | Y | Sum | N | SUM ( 'RETURN MATERIAL LINE'[QTY_TO_RETURN] ) | |
| Rejected Quote Lines Count | N | Y | Count of rows where status is rejected | Sum | N | SUM ( 'SALES QUOTATION LINE'[Count Rejected Quote Lines] ) |
| Released Quote Lines Count | N | Y | Count of rows where status is released | Sum | N | SUM ( 'SALES QUOTATION LINE'[Count Released Quote Lines] ) |
| Sale Unit Price Base | N | Y | Sum | N | SUM ( 'SALES QUOTATION LINE'[Base Sale Unit Price] ) | |
| Sale Unit Price Base Inc Tax | N | Y | Sum | N | SUM ( 'SALES QUOTATION LINE'[Base Unit Price Inc Tax] ) | |
| Sales Quantity Customer | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Customer Sales Quantity] ) | |
| Total Budget Cost | Sum of the amount | N | SUM ( 'MARKETING CAMPAIGN FACT'[Marketing Campaign Total Budget Costs] ) | |||
| Total Cost | Sum of the amount | N | SUM ( 'MARKETING CAMPAIGN FACT'[Marketing Campaign Total Cost] ) | |||
| Total Cost CO Line RC | Y | Y | N | SUMX ( 'CUSTOMER ORDER LINE', DIVIDE ( [REP_CURR_TOTAL_COST], [Currency Rate] ) ) | ||
| Total Cost RC | Y | N | N | [Total Cost CO Line RC] /*+[Total Cost CO Charge RC]*/ - [Total Cost RMA Line RC] /*-[Total Cost RMA Charge RC]*/ | ||
| Total Cost RMA Line RC | Y | N | N | SUMX ( 'RETURN MATERIAL LINE', DIVIDE ( [RMA_Cost_In_Base_Curr], [Currency Rate] ) ) | ||
| Total Cost in Base Curr CO Line | N | Y | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[TOTAL_COST] ) | |
| Total Cost in Base Curr RMA Line | N | Y | Sum | N | SUM ( 'RETURN MATERIAL LINE'[RMA_Cost_In_Base_Curr] ) | |
| Total Line Quantity | N | Y | Sum | N | SUM ( 'SALES QUOTATION LINE'[Quantity Line Total] ) | |
| Total Opportunity Count | N | N | Count of rows which contain a value for opportunity | Count | N | COUNT ( 'BUSINESS OPPORTUNITY'[Opportunity No] ) |
| Total Order Line Discount | N | N | Sum | N | SUM ( 'CUSTOMER ORDER LINE'[TOTAL_ORDER_LINE_DISCOUNT] ) | |
| Total Order Line Discount CO Line RC | Y | Y | N | SUMX ( 'CUSTOMER ORDER LINE', DIVIDE ( [TOTAL_ORDER_LINE_DISCOUNT], [Currency Rate] ) ) | ||
| Total Sales Quotation Line Count | N | N | Count of rows which contain a value for sales quotation line | Count | N | COUNT ( 'SALES QUOTATION LINE'[Line Number] ) |
| Total Value Base | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Total Base Value] ) | |
| Total Value Cost | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Total Cost Value] ) | |
| Total Value Current | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Total Current Value] ) | |
| Unit Price Base Sale | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Base Sale Unit Price] ) | |
| Unit Price Sales | N | Y | Sum | N | SUM ( 'BUSINESS OPP LINE'[Sales Unit Price] ) | |
| VAT Amount Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[VAT_AMOUNT] ) |
| VAT Amount DOM Cust Inv | N | Y | Sum of the amount | Sum | N | SUM ( 'CUSTOMER INVOICE'[VAT_AMOUNT_DOM] ) |
| Won Quote Lines Count | N | Y | Count of rows where status is won | Sum | N | SUM ( 'SALES QUOTATION LINE'[Count Won Quote Lines] ) |
Time based measures are calculated in the cube with respect to other measures. The table below defines the meaning/definition of the different time measures. These measures need to be used with a specific time dimension hierarchy [REPORTING_DATE].[RepDateMFHy].
| Measure | Meaning |
| YTD |
Aggregated value for current month in current year. E.g.
for period 202010 Ytd represents sum of periods 202000-202010. |
| R12 |
For a given period, the sum all months from one year back up to the
previous month. E.g. Rolling 12 for 202010 is the sum of months 201910
- 202009 |
| PY |
Previous Year The value of the corresponding month one year back. E.g. for month 202010, the value of month 201910. |
| YTD PY |
Year to Date Previous Year Aggregated value for corresponding month one year back. E.g. for month 202010 YTD Previous Year represents sum of months 201901-201910. |
| R12 PY |
Rolling 12 (completed) Months Previous Year For a given month, first going one year back, then taking the sum of all months from one (more) year back up to the previous month. E.g. for month 202010 the sum of month 201810-201909 |
| Avg XXX Ytd | Sum XXX YTD/Count XXX YTD |
| Avg XXX R12 | Sum XXX R12/Count XXX R12 |
| XXX % YTD | Count XXX YTD/Count YYY YTD |
| XXX % R12 | Count XXX R12/Count XXX R12 |
| Model Name | |
| CRM |
The data source for this object is defined using IFS Developer Tool. The data source contains a number of transformations where the model reads data from the last step, i.e. the Data source view name (DW Source View Name).
Additional information can be found in the online documentation of Information Sources.
| Model Table | DW Source view (SQL Server) | BI Access View | Referenced Information Source | Recommended Access Type |
| CRM MEASURES | FACT_BUSINESS_ACTIVITY_TM, FACT BUSINESS_LEAD_CONTACT_TM, FACT_BUSINESS_OPP_TM, FACT_BUSINESS_OPP_LINE_TM, FACT_CUSTOMER_INVOICE_TM, FACT_CUSTOMER_ORDER_LINE_TM, FACT_RETURN_MATERIAL_LINE_TM, FACT_SALES_QUOTATION_LINE_TM,FACT_BUSINESS_LEAD_TM, FACT_MARKETING_CAMPAIGN_TM, FACT_MARKETING_CAMP_ACTION_TM, FACT_MKTG_ACTION_RESPONSE_TM, FACT_BUSINESS_OPP_SNAPSHOT_TM | FACT_BUSINESS_ACTIVITY_BI, FACT BUSINESS_LEAD_CONTACT_BI, FACT_BUSINESS_OPP_BI, FACT_BUSINESS_OPP_LINE_BI, FACT_CUSTOMER_INVOICE_BI, FACT_CUSTOMER_ORDER_LINE_BI, FACT_RETURN_MATERIAL_LINE_BI, FACT_SALES_QUOTATION_LINE_BI, FACT_BUSINESS_LEAD_BI, FACT_MARKETING_CAMPAIGN_BI, FACT_MARKETING_CAMP_ACTION_BI, FACT_MKTG_ACTION_RESPONSE_BI, FACT_BUSINESS_OPP_SNAPSHOT_BI | FACT_BUSINESS_ACTIVITY, FACT BUSINESS_LEAD_CONTACT, FACT_BUSINESS_OPP, FACT_BUSINESS_OPP_LINE, FACT_CUSTOMER_INVOICE, FACT_CUSTOMER_ORDER_LINE, FACT_RETURN_MATERIAL_LINE, FACT_SALES_QUOTATION_LINE, FACT_BUSINESS_LEAD, FACT_MARKETING_CAMPAIGN, FACT_MARKETING_CAMP_ACTION, FACT_MKTG_ACTION_RESPONSE, FACT_BUSINESS_OPP_SNAPSHOT | Data Mart |