Rows with EffectiveCost but no ListCost produce negative savings rates in the ADX dashboard

Rewritten after further investigation. The original version of this issue claimed these rows are counted as negative savings and framed the fix as a definition choice. That was wrong — the toolkit already clamps x_TotalSavings per row and already flags the rows via x_SourceChanges. The actual defects are narrower and more concrete, and are described below. Original text remains in the edit history.

⚠️ Problem

Rows that carry a real EffectiveCost with ListCost = 0 break savings and ESR reporting. The dominant case is third-party Marketplace/ISV purchases, where this is structural rather than a data quality problem — there is no Microsoft retail list price for an ISV offering, so the field is empty. It also occurs on amortized commitment usage and ordinary usage rows.

The visible symptom: the ADX dashboard can report a negative effective savings rate.

Observed on three production hubs (~36M, ~28M and ~15M rows). Anonymized as A/B/C below.

Monthly views go negative

Hub	Month	Zero-ListCost rows that month	ESR as the dashboard computes it	ESR via x_TotalSavings
B	2025-12	1	−132.55%	23.24%
B	2023-06	2	−111.51%	14.92%
A	2025-09	4	−102.66%	30.08%
B	2024-03	1	−29.46%	19.47%
B	2023-12	1	−21.09%	16.99%
B	2025-03	1	−16.39%	17.85%
B	2026-04	21	−12.23%	23.69%

Hub B has a negative monthly ESR in 6 of 39 months. A single row is enough — in 2025-12 one row out of 928,734 (0.0001% of the month) takes the rate from +23.24% to −132.55%.

Annual totals hide it, and the gap is growing

Hub	ESR (dashboard)	ESR (x_TotalSavings)	Gap
A	19.84%	25.29%	5.45 pp
B	8.91%	20.55%	11.64 pp
C	8.71%	8.72%	0.01 pp

On hub A the gap widens over time — 0.09–0.18 pp through 2022/early 2023, 3–6 pp from 2024 onward — consistent with ISV/Marketplace spend growing as a share of total.

🛠️ Solution

1. The dashboard bypasses the row-level guard

x_TotalSavings is already clamped at ingestion:

| extend x_TotalSavings = iff(isempty(ListCost) or ListCost == 0 or ListCost - EffectiveCost < 0.0001, real(0), ListCost - EffectiveCost)

Verified: zero rows with a negative x_TotalSavings on any of the three hubs. The guard works.

But dashboard.json aggregates the raw columns and subtracts, which defeats a per-row clamp — sum(ListCost) - sum(EffectiveCost), 15 occurrences against only 3 uses of x_TotalSavings. The Power BI measure x_EffectiveSavingsRate uses x_TotalSavings correctly, so the two surfaces disagree by the amounts above on identical data.

Fix: have the dashboard sum x_TotalSavings (and the corresponding x_NegotiatedDiscountSavings / x_CommitmentDiscountSavings) rather than subtracting raw sums.

2. Ingestion cannot repair these rows, though the logic to do so exists

IngestionSetup_v1_2.kql has a fallback chain that would fix them — ListCost → ContractedCost → EffectiveCost (lines 460–487). It never runs on them, because the whole block sits behind tmp_MissingPrices, which requires a price-sheet lookup key:

| extend tmp_MissingPrices = ProviderName == 'Microsoft'
    and (isempty(ListUnitPrice) or isempty(ContractedUnitPrice) or ListUnitPrice == 0 or ContractedUnitPrice == 0)
    and x_EffectiveUnitPrice != 0
    and not(CommitmentDiscountCategory == 'Spend' and CommitmentDiscountStatus == 'Unused')
    and isnotempty(x_SkuMeterId) and isnotempty(x_SkuOfferId)

Evaluated against the 884 unrepaired rows on hub A:

Condition	Rows passing
ProviderName == 'Microsoft'	884 / 884
a price is missing	870 / 884
x_EffectiveUnitPrice != 0	832 / 884
not unused spend commitment	390 / 884
x_SkuMeterId and x_SkuOfferId both set	14 / 884
all five	0 / 884

The block conflates two independent concerns: a price-sheet lookup, which genuinely needs meter and offer IDs, and a cost fallback, which needs nothing. ISV rows have no meter/offer ID — correctly, there is no Azure price to look up — so they fall out of the gate and lose the fallback too.

Fix: apply the cost fallback outside the lookup gate, to any row with ListCost == 0 and EffectiveCost != 0.

3. The v1.0 path writes no x_SourceValues at all

	IngestionSetup_v1_0.kql	IngestionSetup_v1_2.kql
x_SourceValues assignments	0	1
checkReal() calls	0	10
old_ListCost captured	only in a commented-out TODO	yes (line 368)

IngestionSetup_v1_0.kql:425:

// TODO: Save values before changing -- | extend x_old_ContractedUnitPrice = ..., x_old_ListCost = ListCost, ...

The v1.0 path still repairs ListCost — it just records nothing. On hub A that is 2,387,520 rows with a hub-synthesized ListCost and an empty x_SourceValues, all x_SourceVersion == '1.0'. For that data nobody can tell an exported list price from one the hub derived. The v1.2 path handles this correctly (295,406 recorded repairs, every one from an original value of 0).

Fix: finish the TODO, or document that x_SourceValues is v1.2-only.

4. MissingListCost is not usable as a filter

On hub A, 2,712,206 rows carry the flag but 98.92% of them now have a non-zero ListCost; only 29,280 are still zero. The flag is computed at line 337, before the repair at line 475, so it describes the source export — defensible in itself. Combined with (3), though, it means that on v1.0 data neither the flag nor x_SourceValues distinguishes a repaired row from a broken one.

Fix: either clear the flag when the repair succeeds, or add a distinct marker for "repaired", so the flag can answer "what is still wrong".

ℹ️ Additional context

Two minor items in IngestionSetup_v1_2.kql, noticed while reading the same block:

• Unreachable branch in both cost case statements (lines 473 and 486). The two preceding conditions are exact complements — isnotempty(X) and X != 0 versus isempty(X) or X == 0 — so the final fallback can never be selected. Harmless, but misleading to read.
• Comment/condition mismatch at line 466: the comment reads "If 0 and there's a billed cost and prices are the same", but the condition tests EffectiveCost. Looks like a copy-paste from the line above.

One caution for whoever picks up (2): on some of these rows ContractedCost is already below EffectiveCost in the source export, so list >= contracted >= effective does not hold going in. A fallback does not repair that on its own; it may be worth deciding what the invariant should be.

Start with the 15 raw-cost aggregations in dashboard.json and compare them with the existing x_TotalSavings-based measure. Then read the fallback and gating logic in IngestionSetup_v1_2.kql and the TODO around line 425 in IngestionSetup_v1_0.kql. Done means zero-ListCost rows are handled consistently, source metadata is addressed, and the dashboard no longer reports negative or conflicting savings rates.