Dynamic Multi-Level Ranking & Hierarchy Percentages with Visual Calculations
This visual demonstrates a fully dynamic Matrix analysis built entirely with Visual Calculations. It supports a multi-level hierarchy (Region → Country → City → Store) and automatically adapts as the user drills up or down.
A Selected Metric field parameter drives all Visual Calculations. All rankings, percentages, and totals dynamically recalculate based on the user’s chosen metric (for example: Value or Row Count).
A lightweight Level Label identifies the current row depth (Region, Country, City, Store) using ISINSCOPE. Each ranking VC returns a value only when the Matrix is at the correct level:
- Rank All – ranking across the entire visual
- Rank in Region – shown only at the Country level
- Rank in Country – shown only at the City level
- Rank in City – shown only at the Store level
All ranks are formatted using FORMAT() for a clean whole-number display inside the Matrix.
(If you want to apply conditional formatting, remove the FORMAT wrapper inside the VC.)
You can extend the hierarchy to any number of levels by following the exact same pattern.
The model also includes hierarchical percentage calculations:
- Share of Parent – contribution to the immediate parent node, using COLLAPSE
- Share of Total – contribution to the entire dataset, using COLLAPSEALL
Both percentage measures are formatted with “0.00%” for clear readability.
Visual Calculation functions used:
RANK, ORDERBY, PARTITIONBY, COLLAPSE, COLLAPSEALL, FORMAT, DIVIDE
This pattern provides a clean, dynamic, drill-aware analytical matrix that can be extended further in future posts.
By Hadi Kheireddine
Have fun exploring Visual Calculations!
