Skip to content

A decomposition tree for variance turns one bad number into a named cause

BI and reportingUpdated 2026-08-237 min read

In short

A decomposition tree takes one measure and splits it by a chosen dimension, then splits a child node by another, so a reader walks from a total down to the segment carrying the change. It works only on additive measures, and it shows where a movement sits rather than why it happened.

The Monday after close, registrations came in at 13,482 against 14,382 last edition. Down 900. The show director wanted to know where, and the honest answer at nine in the morning was that nobody knew yet, because finding out meant an analyst writing six queries and pivoting a table.

A decomposition tree for variance is the interface that removes those six queries. It takes one measure, splits it by a dimension you pick, then lets you split any child node by another dimension, so the walk from a total to a named cause happens in clicks. Microsoft describes the visual as one that lets you "visualize data across multiple dimensions", automatically aggregating and drilling in any order, and calls it "valuable for ad hoc exploration and conducting root-cause analysis" (Microsoft Learn, 2026).

The method of attributing an attendance change is D13's subject and it is a genuinely separate piece of work. This post is about the visual, and about four ways it goes wrong.

A 900 registration drop, walked down four levels

Set the measure to registrations delta against prior edition. The root node reads minus 900.

Split by region. EMEA is minus 740, Americas minus 210, APAC plus 50. Those three sum to minus 900, which is the property that makes the walk safe: no residual, nothing unallocated.

Take EMEA and split by acquisition channel. Media partner is minus 690, paid search minus 80, direct plus 30. That sums to minus 740, and one node now holds three quarters of the whole problem.

Take the media partner node and split by badge type. Visitor is minus 660, conference minus 30, summing to minus 690.

Four levels, three clicks, and the answer has a name. One media partner delivered 712 registrations to the prior edition and 22 to this one. The difference is 690, which is 76.7 per cent of the entire shortfall, and it is a partner whose contract lapsed in February.

That is the good case, and the reason to build the visual. The remaining sections are about the cases where the same four clicks produce something confident and wrong.

The measure has to be additive

The first failure is quiet and it is arithmetic.

A decomposition tree assumes that the children of a node sum to the node. That is a property of the measure rather than of the visual. The dimensional modelling taxonomy Kimball and Ross set out in the third edition of The Data Warehouse Toolkit in 2013 is the useful frame: additive measures "can be summed across any of the dimensions associated with the fact table", semi-additive measures "can be summed across some dimensions, but not all", with balances as the standard example because they are additive across everything except time, and "some measures are completely non-additive, such as ratios".

Registrations are additive. Leads are additive. Net square metres sold are additive. Almost every metric an event team actually argues about is a ratio: no-show rate, conversion rate, rebooking rate, leads per square metre, attendance as a percentage of registrations.

Put a no-show rate in the Analyze field and the tree will render. Every node will show a percentage, the visual will look correct, and the children will not sum to the parent, because a weighted average of rates is not the sum of rates. A reader walking that tree will conclude that a region "contributed" a certain number of percentage points and that number will be meaningless.

Kimball's own remedy is the one to apply. Store the additive components and combine them at the end. Build two trees, one on no-shows and one on registrations, walk the same path down both, and compute the rate at whatever node you care about. It is more work and it produces numbers that survive being checked.

Semi-additive measures deserve a warning too. Booked space on a given date is a balance. Summing it across editions produces a number with no meaning, and a tree whose first level is edition will do exactly that without complaint.

Why does the tree stop showing your categories?

The second failure is a documented limit that almost nobody reads.

Microsoft's considerations for the visual state that "the maximum number of levels for the tree is 50", that the maximum number of data points visualised at one time is 5,000, and that levels are truncated: "We truncate levels to show top n. Currently the top n per level is set to 10."

Ten nodes per level. A show with 41 product categories splits into 10 drawn nodes and 31 that are not drawn at any point. If your 900 registration drop had been concentrated in the twenty-third largest category rather than in one media partner, the tree would have shown you ten categories, none of which explained anything, and a reader would have concluded that the drop was spread evenly.

Nothing in the visual says this has happened. The level looks complete.

The practical response is to check the cardinality of every dimension before you put it in Explain by, and to group anything above ten into a parent that has ten or fewer members. Forty-one product categories become eight sectors with an "other" bucket, and the sector level is now honest. Somebody then has to own the grouping, because a grouping invented for a chart and stored in the chart is the sort of definition that diverges from the finance one within two editions.

Which order should the levels go in?

The third issue is that a tree shows one path, and the path is chosen by whoever is clicking.

Walk the same 900 the other way round. Split by channel first and the media partner node reads minus 690 at level one, found in a single click. Split by badge type first and the visitor node reads minus 830, which is true and unhelpful, because it mixes the partner's 660 with 170 of ordinary decline from everywhere else.

Three different orders, three different first impressions, one dataset. That behaviour is inherent to a tree rather than a defect in it, and it means the default path you ship carries an opinion.

Microsoft supports locking levels, so a report author can fix the first levels and let consumers explore below them, with the constraint that "you can't have unlocked levels preceding locked levels". I would use it, and I would lock exactly one level: the dimension your business is actually managed by. For most exhibition portfolios that is acquisition channel or region, because those have owners who can act. Locking three levels turns an exploration tool into a fixed report with extra clicks, and at that point a bar chart is cheaper.

The wider question of how a reader moves from an overview to a single record is V6's path design, and the tree is one station on that route rather than the whole journey.

Where the AI splits help and where they mislead

The visual will choose the next dimension for you. Microsoft's High Value split "considers all available fields and determines which one to drill into to get the highest value of the measure being analyzed", with Low Value doing the reverse, and a Relative mode that looks for values standing out against the rest of the column rather than the largest absolute figure.

For a first pass on an unfamiliar drop, this is genuinely useful. It searches dimensions you would not have thought to try.

Two things to know before quoting it. The splits recalculate whenever the tree is cross-filtered by another visual, so two colleagues with different slicer selections will see different dimensions chosen and both will describe it as what the tree said. And an absolute High Value split gravitates towards whichever segment holds the largest number, which in a portfolio is usually the largest show, whether or not anything happened there. The Relative mode is the better default for variance work, because a category that dropped 40 per cent against a small base is more often the finding than the biggest absolute number.

Ranking factors by their association with an outcome is a different visual with different failure modes, and that belongs to V29 and the key influencers question.

Where this stops

A decomposition tree finds where. It never finds why, and the gap between those two is where most of the damage happens.

The media partner node in the worked example gives you a location and stops there. The partner might have lapsed, might have switched to a competing event, might have run the same campaign into a list that had been suppressed, or might have delivered the same traffic under a different UTM that is now landing in the direct bucket. That last one is the common case and it produces a tree that looks like a story about a lost partner and is actually a story about tagging.

The second limit is that a tree assumes the segments existed in both periods. If you added a region, split a channel or renamed a badge type between editions, the delta at that node is a definition change, and the visual will render it in the same colour as everything else.

Take last edition's registration delta and build a two-level tree on it this week, region then channel, with the measure set to a raw count rather than a rate. Then count the distinct values in every dimension you were tempted to add and cross off anything over ten until you have grouped it. Those two steps take an hour and they remove the two failure modes that actually bite. The rest of the BI and reporting set has the same discipline applied to different visuals.

Questions people ask about decomposition tree for variance

What is a decomposition tree used for in event reporting?
Turning an unexplained movement in a headline figure into a named segment. Registration down 900 is a question. A tree splitting that 900 by region, then by channel, then by badge type, ends on a node small enough to have an owner. Microsoft documents the visual as valuable for ad hoc exploration and root cause analysis.
Can a decomposition tree break down a rate or a percentage?
Not meaningfully. The Kimball Group classes ratios as completely non-additive, and a tree assumes children sum to their parent. Split the numerator and the denominator as separate additive trees, then compute the rate at whatever level you want it. A tree drawn directly on a conversion rate produces nodes whose arithmetic does not hold.
Why does a decomposition tree hide some of my categories?
Because levels are truncated. Microsoft documents the current limit as the top ten nodes per level, with a maximum of 5,000 data points across the tree. On a show with 41 product categories, 31 of them are not drawn at any level, so a drop concentrated in a mid sized category can be invisible.

Related reading

All bi and reporting articles