Introducing new Dynamic Multi-Level Ranking & Hierarchy Percentages with Visual Calculations

gopy6243 — Wed, 19 Nov 2025 12:18:16 GMT

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!

🔗LinkedIn

topic Introducing new Dynamic Multi-Level Ranking & Hierarchy Percentages with Visual Calculations in Visual Calculations Gallery

Introducing new Dynamic Multi-Level Ranking & Hierarchy Percentages with Visual Calculations