Edit

Share via

KPIs and measures in the Power BI Sales app

APPLIES TO: Generally available in Business Central 2024 release wave 2 (version 25.1).

This article lists and describes the key performance indicators (KPIs) included in the semantic model for the Power BI Sales app. The descriptions include how the app calculates KPIs and the data it uses for its calculations.

Explore the KPIs to learn more about how they can help you achieve your business goals.

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.

Customer Table

Customer Measures

No. of Customers

Formula

Distinct count of the Customer No. column from the Sales table.

Data Sources

  • Value Entries
  • Sales Line

No. of Lost Customers

Formula

This measure calculates the number of customers who were lost, meaning customers who stopped purchasing, within the current date selection. It determines the latest date in the selection, prepares a table of customers and the dates they were "lost," and then filters out the customers whose lost date is within the selected date range. Finally, it counts the rows in this filtered table to get the number of lost customers.

Data Sources

  • Value Entries
  • Sales Line

No. of New Customers

Formula

This measure counts new customers by finding the date of their first purchase. It includes customers if their first purchase happened within the date selection.

Data Sources

  • Value Entries
  • Sales Line

No. of Recovered Customers

Formula

This measure calculates the number of customers who were temporarily lost, that is, customers who stopped purchasing but later made a new purchase. It identifies customers who had a "lost" date before the selected date range, then determines which customers made a new purchase within the current period. The measure filters and counts only those customers whose new purchase occurred after their lost date, returning the total number of recovered customers.

Data Sources

  • Value Entries
  • Sales Line

No. of Returning Customers

Formula

This measure calculates the number of customers who made a repeat purchase during the current period. It identifies existing customers by filtering customers whose first purchase was before the selected date range, then determines which of these customers made another purchase within the current period. The measure returns the count of these returning customers.

Data Sources

  • Value Entries
  • Sales Line

Sales Lost Customers (12M)

Formula

This measure calculates the total sales lost over the past 12 months due to customers who stopped purchasing ("lost" customers). It first identifies the most recent "lost" date, then filters customers who were lost before that date. It gets the sales from the previous 12 months for these lost customers and calculates the total revenue they generated before they stopped purchasing.

Data Sources

  • Value Entries
  • Sales Line

Sales New Customers

Formula

This measure calculates the total sales from customers who made their first purchase within the current date range. It identifies each customer's first purchase date, filters out customers whose first purchase occurred during the selected period, and then sums the sales amount from those new customers.

Data Sources

  • Value Entries
  • Sales Line

Sales Recovered Customers

Formula

This measure calculates the total sales generated by recovered customers. Recovered customers were previously lost but made a purchase afterward. It identifies temporarily lost customers, checks whether they made a new purchase in the current period, and then sums the sales figures for them.

Data Sources

  • Value Entries
  • Sales Line

Sales Returning Customers

Formula

This measure calculates the total sales generated by returning customers. Returning customers are customers who made their first purchase before the current period and made more purchases in the current period. It identifies returning customers by finding the intersection of active customers and customers who purchased previously, and then sums the sales from these returning customers.

Data Sources

  • Value Entries
  • Sales Line

Opportunities Table

Opportunities Measures

Opportunity Sales Quote Amount

Formula
This measure calculates the sales amount where the Document Type is Quote and the Sales Opportunity No. is related to an Opportunity No.

Data Sources

  • Opportunity Entries
  • Sales Line

Opportunity Entries Table

Counters

Opportunity Entry Measures

No. of Lost Opportunities

Formula

Distinct count of Opportunity No. column from the Opportunity Entries table, where Active = true and the Action Taken = Lost.

Data Sources

  • Opportunity Entries

No. of Opportunities

Formula
Distinct count of the Opportunity No. column from the Opportunity Entries table.

Data Sources

  • Opportunity Entries

No. of Won Opportunities

Formula

Distinct count of the Opportunity No. column from the Opportunity Entries table where Active is selected and Action Taken is Won.

Data Sources

  • Opportunity Entries

Active Calculated Current Value

Formula

Sum of the Calculated Current Value from the Opportunity Entries table, where Active is selected.

Data Sources

  • Opportunity Entries

Active Chance of Success Percent

Formula

Average of the Chance of Success from the Opportunity Entries table, where Active is selected.

Data Sources

  • Opportunity Entries

Active Estimated Value

Formula

Sum of the Estimated Value from the Opportunity Entries table, where Active os selected.

Data Sources

  • Opportunity Entries

Average Chance of Success Percent

Formula

Average of the Chance of Success from the Opportunity Entries table.

Data Sources

  • Opportunity Entries

Average Completed Percent

Formula

Average of the Completed value from the Opportunity Entries table.

Data Sources

  • Opportunity Entries

Average Probability Percent

Formula

Average of the Probability value from the Opportunity Entries table.

Data Sources

  • Opportunity Entries

Calculated Current Value

Formula

Sum of the Calculated Current Value from the Opportunity Entries table.

Data Sources

  • Opportunity Entries

Estimated Value

Formula

Sum of the Estimated Value from the Opportunity Entries table.

Data Sources

  • Opportunity Entries

Win Loss Percent

Formula

The No. of Won Opportunities divided by the sum of the No. of Won Opportunities and the No. of Lost Opportunities.

Data Sources

  • Opportunity Entries

Sales Table

Counters

Sales Measures

Invoiced Amount

Formula

The sum of the Sales Amount column from the Sales table where the Source Type is Value Entries Invoiced.

Data Source

  • Value Entries

Invoiced Quantity

Formula

The sum of the Sales Qty. (Base) column from the Sales table where the Source Type is Value Entries Invoiced.

Data Source

  • Value Entries

Outstanding Amount

Formula

The sum of the Sales Amount column from the Sales table where the Source Type is Sales Order Outstanding.

Data Source

  • Sales Line

Outstanding Quantity

Formula

The sum of the Sales Qty. (Base) column from the Sales table where the Source Type is Sales Order Outstanding.

Data Source

  • Sales Line

Shipped Not Invoiced Amount

Formula

The sum of the Sales Amount column from the Sales table where the Source Type is Sales Order Shipped Not Invoiced.

Data Source

  • Sales Line

Shipped Not Invoiced Quantity

Formula

The sum of the Sales Qty. (Base) column from the Sales table where the Source Type is Sales Order Shipped Not Invoiced.

Pareto Color Cutoff

Formula

The color cutoff where Pareto Customer Rank by Gross Profit exceeds the Pareto Customer Rank by Gross Profit.

Data Source

  • Sales Line

Pareto Cumulative Gross Profit

Formula

The Cumulative Gross Profit based on the Pareto Customer Rank by Gross Profit.

Data Source

  • Sales Line

Pareto Cumulative Gross Profit Percent

Formula

The Cumulative Gross Profit divided by the Total Gross Profit.

Data Source

  • Sales Line

Pareto Customer Cutoff Rank

Formula

The maximum Pareto Customer Rank based on the Pareto Cutoff Customer Rank.

Data Source

  • Sales Line

Pareto Customer Rank by Gross Profit

Formula

The Pareto Customer Rank ordered by the Gross Profit on the Sales table.

Data Source

  • Sales Line

Pareto Cutoff Cumulative Gross Profit

Formula

The Cumulative Gross Profit for the Pareto Cutoff Customer.

Data Source

  • Sales Line

Pareto Cutoff Customer

Formula

The Customer with the maximum difference of the Pareto Cumulative Gross Profit minus the Pareto Value.

Data Source

  • Sales Line

Pareto Cutoff Customer Rank

Formula

The Pareto Customer Rank by Gross Profit for the Pareto Cutoff Customer.

Data Source

  • Sales Line

Pareto Cutoff Gross Profit Percent

Formula

The Pareto Cumulative Gross Profit for the Pareto Cutoff Customer.

Data Source

  • Sales Line

Pareto Total Gross Profit

Formula

The Total Gross Profit from the Sales table for all sales.

Data Source

  • Sales Line

Data Source

  • Sales Line

Sales Budget Table

Budget Measures

Budget Amount

Formula

The sum of the Sales Amount column from the Sales Budget table.

Data Source

  • Item Budget Entries

Budget Amount Variance

Formula

The Sales Amount minus the Budget Amount.

Data Sources

  • Item Budget Entries
  • Sales Lines
  • Value Entries

Budget Amount Variance Percent

Formula

The Budget Amount Variance minus the Budget Amount.

Data Sources

  • Item Budget Entries
  • Sales Lines
  • Value Entries

Budget Quantity

Formula

The sum of the Quantity column from the Sales Budget table.

Data Source

  • Item Budget Entries

Budget Quantity Variance

Formula

The Sales Quantity minus the Budget Quantity.

Data Sources

  • Item Budget Entries
  • Sales Lines
  • Value Entries

Budget Quantity Variance Percent

Formula

The Budget Quantity Variance minus the Budget Quantity.

Data Sources

  • Item Budget Entries
  • Sales Lines
  • Value Entries

No. of Distinct Items

Formula

The count of the Item No. column from the Sales table.

Data Sources

  • Value Entries
  • Sales Line

No. of Outstanding Sales Orders

Formula

The count of the Document No. column from the Sales table where the Document Type is Order and the Source Type is Sales Order Outstanding.

Data Sources

  • Value Entries
  • Sales Line

No. of Posted Sales Invoices

Formula

The count of the Document No. column from the Sales table where the Document Type is Sales Invoice and the Source Type is Value Entries Invoiced.

Data Sources

  • Value Entries
  • Sales Line

No. of Return Orders

Formula

Distinct count of the Document No. column from the Sales table, where the Document Type is Return Order.

Data Sources

  • Value Entries
  • Sales Line

No. of Sales Quotes

Formula

Distinct count of the Document No. column from the Sales table, where the Document Type is Sales Quote.

Data Sources

  • Value Entries
  • Sales Line

No. of Shipped Not Invoiced Sales

Formula

The count of the Document No. column from the Sales table where the Document Type is Order and the Source Type is Sales Order Shipped Not Invoiced.

Data Sources

  • Value Entries
  • Sales Line

Cost Amount

Formula

The sum of the Cost Amt. (LCY) column from the Sales table.

Data Sources

  • Value Entries
  • Sales Line

Cost Amount Non-Inv

Formula

The sum of the Cost Amt. Non-Invtbl. (LCY) column from the Sales table.

Data Source

  • Value Entries

Gross Profit

Formula

The Sales Amount minus the Cost Amount minus the Cost Amount Non-Inv.

Data Sources

  • Value Entries
  • Sales Line

Gross Profit MTD (Fiscal)

Formula

This measure calculates month-to-date gross profit using the fiscal calendar, considering only the sales up to the last available fiscal day of the current month and year.

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Gross Profit Margin

Formula

The Gross Profit divided by the Sales Amount.

Data Sources

  • Value Entries
  • Sales Line

Return Rate

Formula

This measure calculates the sales amount where the Document Type is Sales Credit Memo, divided by the sales amount where the Document Type is Sales Invoice.

Data Sources

  • Value Entries
  • Sales Line

Sales Amount

Formula

The sum of the Sales Amt. (LCY) column from the Sales table.

Data Sources

  • Value Entries
  • Sales Line

Sales Quantity

Formula

The sum of the Sales Qty. (Base) column from the Sales table.

Data Sources

  • Value Entries
  • Sales Line

Sales Amount MTD (Fiscal)

Formula

This measure calculates the month-to-date sales amount using the fiscal calendar. It considers sales up to the last available fiscal day of the current month and year.

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Sales Amount MAT (Fiscal)

Formula

This measure calculates the sales for the last 12 months (moving annual total). It uses the fiscal calendar and sums the sales between the calculated first and last days of the 365-day period.

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Sales Amount PYMAT (Fiscal)

Formula

This measure calculates the total sales for the prior 12 months based on the fiscal calendar. It then determines the maximum available date and calculates the range from the previous 24 months to the last available day to 12 months before. It uses these dates to sum the sales amount for this period while maintaining filters for the day type and weekday.

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Sales Amount MATG (Fiscal)

Formula

This measure calculates the year-over-year growth in sales by comparing the current period's moving annual total sales to the previous period's moving annual total sales. If both values aren't blank, it subtracts the previous period's sales from the current period's sales to determine the growth. The result is the change in sales between the two periods.

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Sales Amount MATG % (Fiscal)

Formula

This measure calculates the percentage growth in sales by dividing the year-over-year growth in sales (Sales Amount MATG (Fiscal)) by the previous period's moving annual total sales (Sales Amount PYMAT (Fiscal)).

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Sales Amount AVG 30D (Fiscal)

Formula

This measure calculates the average sales over the last 30 days based on the fiscal calendar. The measure determines the maximum day in the dataset and defines the 30-day period ending on that day. It gets the days within this range while maintaining filters for day type and weekday. It also identifies the first day with sales data to ensure valid calculations. If the first day with data is within the 30-day period, it calculates the average sales amount over those days and returns the result.

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Sales Amount PP (Fiscal)

Formula

This measure determines the appropriate sales amount based on the current context of the date hierarchy in the fiscal calendar. It uses the SWITCH function to evaluate whether the context is at the fiscal month, fiscal quarter, or fiscal year level. Depending on which level is active, it returns the corresponding sales amount, which is either:

  • Previous month (Sales Amount PM (Fiscal))
  • Previous quarter (Sales Amount PQ (Fiscal))
  • Previous year (Sales Amount PY (Fiscal))

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Sales Amount POP (Fiscal)

Formula

This measure calculates the change in sales between the current period and the previous period based on the context of the fiscal calendar. It uses the SWITCH function to determine the appropriate previous period calculation depending on whether the current context is at the fiscal month, quarter, or year level. When at the fiscal month level, it references the month-over-month change from the Sales Amount MOM (Fiscal) measure, which computes the difference between the current month's sales and the previous month's sales. Similarly, for fiscal quarters and years, it uses quarter-over-quarter and year-over-year calculations, respectively.

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

Sales Amount POP % (Fiscal)

Formula

This measure calculates the percentage growth in sales between the current and previous period based on the context of the fiscal calendar. It uses the SWITCH function to determine the appropriate previous period calculation depending on whether the current context is at the fiscal month, quarter, or year level. When at the fiscal month level, it references the month-over-month change from the Sales Amount MOM % (Fiscal) measure, which determines the percentage growth between the current month's sales and the previous month's sales. Similarly, for fiscal quarters and years, it uses quarter-over-quarter and year-over-year calculations, respectively.

Data Sources

  • Value Entries
  • Sales Line
  • Date (Fiscal Calendar)

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

Find free e-learning modules for Business Central here