Edit

Share via

KPIs and measures in the Power BI Sales app

  • Article

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" (stopped purchasing) within the current date selection. It determines the latest date in the selection, prepares a table of customers and their associated "lost" dates, 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" (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

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 Sales Order Shipped Not Invoiced.

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 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

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)

See also

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