Universal Composite KPI : Weighted & Normalized Scoring Using Visual Calculations Only
This pattern creates a base solution for Composite KPI Score by normalizing multiple KPIs to a 0–1 scale and applying user-defined weights via What-If parameters(numeric field parameters). The result is a single, comparable performance indicator that works across different KPI types and units, fully powered by Visual Calculations.
Concept Overview
Because KPIs have different scales (money, percentages, counts), each KPI is first normalized, then multiplied by a weight.
The final composite score is a weighted sum of all normalized KPIs.
Normalization Formula
1. Min–Max Normalization (Higher Is Better)
This method maps all values into the range 0 to 1, where 0 is the minimum and 1 is the maximum:
Use this when higher values represent better performance (e.g., Sales, Orders, Margin, CSAT).
2. Min–Max Normalization (Lower Is Better)
Some KPIs—such as Total Cost, Defects, Error Rate, or Response Time—improve when the value is lower.
In these cases, the formula must be inverted so that the lowest value receives the highest normalized score:
This keeps everything in the same 0–1 range, while correctly rewarding lower values.
3. Z-Score Normalization (Standardization)
Alternatively, you can normalize using Z-scores, which measure how far a value is from the mean:
Z-scores:
- Are centered around 0
- Can be negative or greater than 1
- Are useful when you care about distance from average, not just min/max boundaries
For KPIs where lower is better, you simply invert the sign:
example code is provided for Z normalization in the file but not used in the composition
Composite Score Formula
Visual Calculation Functions Used
- FIRST()
- ORDERBY()
- DIVIDE()
- Basic arithmetic operations (multiplication, addition)
Pros
- Fair comparison across KPIs with different units
- User-controlled weight influence
- Fully dynamic within the visual
- 100% implemented using Visual Calculations
Cons
- You must repeat the normalization pattern for each KPI you want to include.
- Each added KPI requires creating a new normalization VC (unless automated in a future pattern).
Future Post Enhancements pattern
- Allow the user to select which KPIs to include in the composite score.
- Allow the user to input weights only for selected KPIs.
- Make the normalization and composite score automatic, so adding new KPIs does not require additional Visual Calculations. It accepts selected measures as parameter and compute for all.
By Hadi Kheireddine
