Learn Dynamic KPI Engine | Scenario Modeling and Interactive Decision Systems

Swipe to show menu

The workbook already supports reporting, scenario modeling, and sensitivity analysis. In this chapter, combine these systems into a centralized KPI engine where all outputs respond dynamically to user inputs.

KPI Engine Structure

A dynamic KPI system separates the workbook into three layers:

Inputs: user selections;
Logic: calculations and formulas;
Outputs: displayed KPIs.

This structure keeps the model scalable and easy to control.

SUMIFS and COUNTIFS Structure

=SUMIFS(sum_range, criteria_range1, criteria1, ...)
=COUNTIFS(criteria_range1, criteria1, ...)

These functions aggregate values dynamically based on selected filters.

FILTER, MAX and XLOOKUP Logic

Top-product calculations combine multiple functions:

FILTER → MAX → XLOOKUP

FILTER: isolates matching rows;
MAX: identifies the highest value;
XLOOKUP: retrieves the related label.

Step 1 Build the KPI Panel

Inside the Summary sheet, create a new section:

Dynamic KPI Panel

Step 2 Create the Input Layer

Build the following input structure:

Input	Source
Active Scenario	Scenarios!B7
Selected Region	Reference_Lists dropdown
Selected Month	Whole number (1–12)

Step 3 Calculate Filtered Revenue

Type:

=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth])

Sales_Data[Region]: region filter;
Sales_Data[Month]: month filter.

The KPI now responds dynamically to both selections.

Step 4 Calculate Filtered Orders

Type:

=COUNTIFS(Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth])

The KPI returns the number of matching transactions.

Step 5 Calculate Average Order Value

Type:

=IFERROR([FilteredRevenue]/[FilteredOrders],0)

[FilteredRevenue]: filtered Revenue KPI;
[FilteredOrders]: filtered order count;
0: fallback value when no orders exist.

Step 6 Find the Top Product

Type:

=XLOOKUP(MAX(FILTER(Sales_Data[Revenue],Sales_Data[Region]=[SelectedRegion])),Sales_Data[Revenue],Sales_Data[Product],"No data")

FILTER(...): isolates the selected region;
MAX(...): identifies the highest Revenue value;
XLOOKUP(...): returns the matching product.

Step 7 Build Projected Revenue

Type:

=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth]) * Scenarios!Active Price Multiplier * Scenarios!Active Volume Multiplier

Base Revenue: filtered transactional output;
Price multiplier: active scenario adjustment;
Volume multiplier: active scenario adjustment.

The KPI now models projected Revenue dynamically.

Step 8 Test the Full KPI System

Change:

Region;
Month;
Scenario.

Confirm that:

Revenue updates automatically;
Order counts update automatically;
Average Order Value updates automatically;
Top Product updates automatically;
Projected Revenue recalculates instantly.

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 3

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 5. Chapter 3

Dynamic KPI Engine

KPI Engine Structure

SUMIFS and COUNTIFS Structure

FILTER, MAX and XLOOKUP Logic

1. What is the main purpose of a KPI engine?

2. Why is `IFERROR` used in KPI calculations like Average Order Value?

3. Why is `FILTER` used inside the Top Product calculation?

Dynamic KPI Engine

KPI Engine Structure

SUMIFS and COUNTIFS Structure

FILTER, MAX and XLOOKUP Logic

1. What is the main purpose of a KPI engine?

2. Why is IFERROR used in KPI calculations like Average Order Value?

3. Why is FILTER used inside the Top Product calculation?

2. Why is `IFERROR` used in KPI calculations like Average Order Value?

3. Why is `FILTER` used inside the Top Product calculation?