Power BI Sales app semantic model

APPLIES TO: Generally available in Business Central 2024 release wave 2.

The semantic model in the Power BI Sales app uses a star schema. Learn more in Understand star schema and the importance for Power BI.

The fact tables contain information about individual transactions from sources such as sales orders, posted sales invoices, and posted credit memos from Business Central. The following sections describe selected fields rather than every field in the model.

The dimension tables provide more context and attributes to the transactional data, such as customer and product information.

Fact tables

Fact tables store transactional data and support summarizations such as SUM, AVG, COUNT, and more. There are several fact tables in the Power BI Sales app:

Opportunity entries

The app uses data from the following table:

  • Opportunity Entries

Table definition

Power BI Field NameBusiness Central Field NameDescription
Action TakenAction TakenSpecifies what happened when the opportunity entry was last updated.
ActiveActiveSpecifies that the opportunity entry is active.
Date of ChangeDate of ChangeSpecifies the date when the opportunity entry was last changed.
Estimated Close DateEstimated Close DateSpecifies the estimated date when the opportunity entry will close.
Opportunity Entry No.Entry No.Specifies the opportunity entry's number.
Opportunity No.Opportunity No.Specifies the opportunity's number.

Project ledger entries

The app uses data from the following table:

  • Project Ledger Entries

Table definition

Power BI field nameBusiness Central field nameDescription
DescriptionDescriptionSpecifies the description of the project ledger entry.
Project Ledger Entry No.Entry No.Specifies the entry number of the project ledger entry.
Reason CodeReason CodeSpecifies the reason code, a supplementary source code that enables you to trace the entry.
Unit Cost (LCY)Unit Cost (LCY)Specifies the cost, in LCY, of one unit of the item or resource on the line.

Sales credit lines

The app uses data from the following table:

  • Sales Cr. Memo Line

Table definition

Power BI field nameBusiness Central field nameDescription
DescriptionDescriptionSpecifies the name of the item or general ledger account, or some descriptive text.
Sales Credit Line No.Line No.Specifies the line number of the posted sales credit memo line.

Sales invoice lines

The app uses data from the following table:

  • Sales Invoice Line

Table definition

Power BI field nameBusiness Central field nameDescription
DescriptionDescriptionSpecifies the name of the item or general ledger account, or some descriptive text.
Sales Invoice Line No.Line No.Specifies the line number of the posted sales invoice line.

Sales lines

The app uses data from the following tables:

  • Sales Line
  • Sales Header

Table definition

Power BI field nameBusiness Central field nameDescription
Campaign No.Campaign No.Specifies the campaign number the document is linked to.
DescriptionDescriptionSpecifies a description of the entry of the product to be sold.
Document DateDocument DateSpecifies the date when the related document was created.
Due DateDue DateSpecifies when the sales invoice must be paid.
Order DateOrder DateSpecifies the order date on the sales document
Planned Delivery DatePlanned Delivery DateSpecifies the planned delivery date on the sales line.
Planned Shipment DatePlanned Shipment DateSpecifies the planned shipment date on the sales line.
Posting DatePosting DateSpecifies the date when the posting of the sales document will be recorded.
Promised Delivery DatePromised Delivery DateSpecifies the promised delivery date on the sales document.
Quote No.Quote No.Specifies the number of the sales quote that the sales order was created from.
Requested Delivery DateRequested Delivery DateSpecifies the date that the customer has asked for the order to be delivered.
Sales Line No.Line No.Specifies the line number of the sales line.

Sales value entries

The app uses data from the following tables:

  • Value Entries
  • Item Ledger Entries

Table definition

Power BI field nameBusiness Central field nameDescription
Entry TypeEntry TypeSpecifies the type of value described in this entry.
Item Ledger Entry No.Entry No.Specifies the number of the entry, of the related item ledger entry.
Item Ledger Entry TypeEntry TypeFrom the related Item Ledger Entry, specifies which type of transaction that the entry is created from.
Value Entry No.Entry No.Specifies the number of the entry on the value entry table.

Sales

Note

This fact table is deprecated in Business Central 2025 release wave 2 and is no longer available.

The app uses data from the following tables:

  • Value Entries
  • Sales Lines

Sales lines are broken down into Shipped Not Invoiced and Outstanding Sales Lines. Use the Source Type field to filter on these sources.

Table definition

Power BI field nameBusiness Central field nameDescription
DateOrder Date / Posting DateThe Order Date is used for data from sales lines. The Posting Date is used for data from value entries.
Document No.Document No.Specifies the document number.
Document TypeDocument TypeSpecifies the document type.
Entry No.Entry No. / Sales Line No.When reporting on value entries, this is the value entry number. When reporting on sales lines, this is the sales line number.
Entry TypeEntry TypeWhen reporting on value entries, this is the entry type of the value entry. When reporting on sales lines, the value reads Sales Line.
Source Type-Defines the source table of the record.

Sales budget

The app uses data from the following table:

  • Item Budget Entries

Table definition

Power BI field nameBusiness Central field nameDescription
Budget NameBudget NameSpecifies the name of the budget.
entryDateDateSpecifies the date for the budget entry.
Entry No.Entry No.The unique number of the specific budget entry.

Dimension tables

The star schema model uses dimension tables and allows you to filter and group.

Close opportunity code

The app uses data from the following table:

  • Close Opportunity Code

Table definition

Power BI field nameBusiness Central field nameDescription
Close Opportunity CodeCodeSpecifies the code for closing the opportunity.
Close Opportunity DescriptionDescriptionSpecifies the description of the reason for closing the opportunity.
Close Opportunity TypeTypeSpecifies whether the opportunity was a success or failure.

Contact

The app uses data from the following table:

  • Contact

Table definition

Power BI field nameBusiness Central field nameDescription
Contact NameNameSpecifies the contact name.
Contact No.No.Specifies the number for the contact.
Contact No. & Name-Specifies a combination of contact number and contact name in Power BI.
Contact TypeTypeSpecifies the contact type, either company or person.
Contact Company NameCompany NameSpecifies the name of the company for the contact.
Contact Company No.Company No.Specifies the number for the contact's company.
Contact AddressAddressSpecifies the address for the contact.
Contact Address 2Address 2Specifies the secondary address details for the contact.
Contact CityCitySpecifies the city for the contact.
Contact Country/Region CodeCountry/Region CodeSpecifies the country/region the contact's address is in.
Contact CountyCountySpecifies the state for the contact.
Contact Post CodePost CodeSpecifies the postal code of the address.

Customer

The app uses data from the following table:

  • Customer

Table definition

Power BI field nameBusiness Central field nameDescription
Customer No.No.Specifies the customer number.
Customer NameNameSpecifies the customer name.
Customer No. & Name-Specifies a combination of customer number and customer name in Power BI.
Customer AddressAddressSpecifies the address.
Customer Address 2Address 2Specifies the secondary address details.
Customer CityCitySpecifies the city.
Customer Post CodePost CodeSpecifies the postal code of the address.
Customer CountyCountySpecifies the state.
Customer Country/Region CodeCountry/Region CodeSpecifies the country/region the address is in.
Customer Posting GroupCustomer Posting GroupSpecifies the posting group.
Customer Price GroupCustomer Price GroupSpecifies the price group.
Customer Discount GroupCustomer Disc. GroupSpecifies the discount group.

Documents

The app uses data from the following tables:

  • Sales Lines
  • Value Entries
  • Invoice Lines
  • Sales Cr. Memo Lines
  • Project Ledger Entries

Table definition

Power BI field nameBusiness Central field nameDescription
Document No.Document No.Specifies the document number from the related fact table.
Document TypeDocument TypeSpecifies the type of document related to the fact.
Source Table-Specifies the source table where the document record originated from.

G/L accounts

The app uses data from the following tables:

  • G/L Account

Table definition

Power BI field nameBusiness Central field nameDescription
G/L Account Income/BalanceIncome/BalanceSpecifies whether a general ledger account is an income statement account or a balance sheet account.
G/L Account NameNameSpecifies the name of the general ledger account.
G/L Account No.No.Specifies the no. of the general ledger account.
G/L Account No. and Name-Specifies a concatenated name generated by combining the G/L Account No. and Name columns
G/L Account TypeTypeSpecifies the purpose of the account such as Posting, Heading, Total, Begin-Total or End-Total.

Item

The app uses data from the following tables:

  • Item

Table definition

Power BI field nameBusiness Central field nameDescription
Item No.No.Specifies the item number.
Item DescriptionDescriptionSpecifies the item description.
Item No. & Description-Specifies a combination of item number and description in Power BI.
Item Base Unit of MeasureBase Unit of MeasureSpecifies the base unit of measure.
Item Unit Cost (LCY)Unit CostSpecifies the unit cost in local currency.
Item Inventory Posting GroupInventory Posting GroupSpecifies the inventory posting group.

Item category

The app uses data from the following tables:

  • Item Category

Table definition

Power BI field nameBusiness Central field nameDescription
Item Category CodeCodeSpecifies the code for the item category.
Item Category DescriptionDescriptionSpecifies the description of the item category.
Item Category Code Level 1-Specifies the first level of the item category code hierarchy, based on the parent category.
Item Category Code Level 2-Specifies the second level of the item category code hierarchy.
Item Category Code Level 3-Specifies the third level of the item category code hierarchy.

Location

The app uses data from the following table:

  • Location

Table definition

Power BI field nameBusiness Central field nameDescription
Location CodeCodeSpecifies the ID of the location.
Location NameNameSpecifies the name of the location.

Opportunities

The app uses data from the following table:

  • Opportunity

Table definition

Power BI field nameBusiness Central field nameDescription
Opportunity No.No.Specifies the number of the opportunity.
Opportunity DescriptionDescriptionSpecifies the description of the opportunity.
Opportunity ClosedClosedSpecifies that the opportunity is closed.
Opportunity Creation DateCreation DateSpecifies the date the opportunity was created.
Opportunity Sales Document No.Sales Document No.Specifies the number of the sales document assigned to the opportunity.
Opportunity Sales Document TypeSales Document TypeSpecifies the type of the sales document assigned to the opportunity.
Opportunity StatusStatusSpecifies the status for the opportunity.

Project

The app uses data from the following table:

  • Job

Table definition

Power BI field nameBusiness Central field nameDescription
Project Bill to Customer No.Bill-to Customer No.Specifies the number of the customer who pays for the project.
Project BlockedBlockedSpecifies that the related Project is blocked from being posted in transactions.
Project CompleteCompleteSpecifies if the current status of the project is complete.
Project Creation DateCreation DateSpecifies the date on which the project was set up.'
Project DescriptionDescriptionSpecifies a short description of the project.
Project Ending DateEnding DateSpecifies the date on which the project is expected to be completed.
Project No.No.Specifies the number of Project record.
Project No. & Description-Specifies a combination of the project number and description.
Project Posting GroupJob Posting GroupSpecifies the posting group that links transactions made for the project with the appropriate general ledger accounts according to the general posting setup.
Project Project ManagerProject ManagerSpecifies the person who is assigned to manage the project.
Project Starting DateStarting DateSpecifies the date on which the project actually starts.
Project StatusStatusSpecifies a current status of the project.

Reason codes

The app uses data from the following table:

  • Return Reason

Table definition

Power BI field nameBusiness Central field nameDescription
Return Reason CodeCodeSpecifies the code for the return reason.
Return Reason DescriptionDescriptionSpecifies the description for the return reason.

Resources

The app uses data from the following table:

  • Resource

Table definition

Power BI field nameBusiness Central field nameDescription
Resource Base Unit of MeasureBase Unit of MeasureSpecifies the base unit used to measure the resource.
Resource NameNameSpecifies a description of the resource.
Resource No.No.Specifies the number of the resource record.
Resource No. and Name-Specifies a concatenated name generated by combining the resource no. and name
Resource Unit CostUnit CostSpecifies the cost of one unit of resource.
Resource Unit PriceUnit PriceSpecifies the price of one unit of the resource.

Sales cycle stages

The app uses data from the following table:

  • Sales Cycle Stage

Table definition

Power BI field nameBusiness Central field nameDescription
Sales Cycle CodeSales Cycle CodeSpecifies the sales cycle code for the sales cycle stage.
Sales Cycle StageStageSpecifies the number for the stage within the sales cycle.
Sales Cycle Stage DescriptionDescriptionSpecifies the description for the sales cycle stage.

Salesperson

The app uses data from the following table:

  • Salesperson/Purchaser

Table definition

Power BI field nameBusiness Central field nameDescription
Salesperson CodeCodeSpecifies the ID of the salesperson.
Salesperson NameNameSpecifies the name of the salesperson.

Type

The app uses data from the following tables:

  • Item
  • Resource
  • G/L Account

Table definition

Power BI field nameBusiness Central field nameDescription
No.No. or Account No.Specifies the code of the item, resource, or G/L account.
Type-Specifies the type of object being sold
Type Key-Specifies a concatenated value generated by combining the No and Type columns.

Tip

You can easily track the KPIs that the Power BI reports display against your business objectives. To learn more, go to Track your business KPIs with Power BI metrics.

Track your business KPIs with Power BI goals
Ad-hoc analysis of sales data
Built-in sales reports
Sales analytics overview
Sales overview

Find free e-learning modules for Business Central here