Data Explorer dashboard: multi-currency support — savings and cost KPIs silently produce wrong results

Summary

The Data Explorer dashboard (dashboard.json) aggregates cost columns across billing currencies without normalization or filtering. For multi-currency environments, all cost totals and savings KPIs are mathematically invalid.

The data layer already carries BillingCurrency, PricingCurrency, exchange rates, and USD-normalized columns — but the dashboard ignores them entirely.

Severity

High for affected customers; low visibility because the majority of single-currency EA/MCA customers never notice.

Who is affected

Scenario	Risk
Single EA enrollment (one currency)	✅ Safe
Single MCA billing account	✅ Safe
Multiple billing accounts (EA→MCA transition, M&A)	⚠️ Wrong totals
MCA with multiple billing profiles in different currencies	⚠️ Wrong totals
CSP/MPA partners with customers in multiple currencies	🔴 Orders of magnitude wrong
Multi-cloud (AWS USD + Azure EUR via FOCUS)	⚠️ Wrong totals

Worst-case example (CSP partner)

• US customer: List=$1M, Effective=$500K → 50% savings
• Japan customer: List=¥100M, Effective=¥100M → 0% savings (~$667K at ¥150/USD)
• Dashboard ESR: 500K / (1M + 100M) ≈ 0.5%
• True USD-normalized ESR: 500K / (1M + 667K) ≈ 30%

Root cause

The CostsPlus base query (which feeds CostsByMonth, CostsByDay, and 74 of 86 dashboard queries) calls Costs_v1_2 without any currency filter. All downstream queries then do:

| summarize
    ListCost = sum(ListCost),
    ContractedCost = sum(ContractedCost),
    EffectiveCost = sum(EffectiveCost)
// ...then compute savings:
| extend TotalSavings = ListCost - EffectiveCost
| extend EffectiveSavingsRate = TotalSavings / ListCost

These sums aggregate across all BillingCurrency values without grouping or filtering.

Specific issues found

1. ~66+ of 86 queries aggregate cost columns without currency awareness
2. Savings calculations recompute from raw cost sums rather than using canonical row-level fields (x_CommitmentDiscountSavings, x_NegotiatedDiscountSavings, x_TotalSavings) which have built-in clamping logic
3. Percentage metrics (ESR) are NOT safe — the ratio of mixed-currency sums ≠ the weighted average of per-currency rates
4. Savings-plan utilization uses coalesce(x_BillingExchangeRate, 1) — silently assumes 1:1 when rate is missing, distorting utilization by the FX factor
5. No guardrail — no dcount(BillingCurrency) warning or multi-currency detection anywhere in the dashboard
6. Only 1 query (Purchases detail table) even displays BillingCurrency as a column — and it is display-only, not a filter

Proposed fix (phased)

Phase 1 — Pragmatic fix (recommended)

1. Add a BillingCurrency query-based parameter — source from Costs_v1_2 | distinct BillingCurrency; apply filter in CostsPlus base query so it propagates to all 74+ downstream queries
2. Add a mixed-currency warning tile — CostsPlus | summarize dcount(BillingCurrency) | where dcount > 1 to alert users when multiple currencies are in scope
3. Fix savings formulas — replace inline sum(ListCost) - sum(EffectiveCost) patterns with sum(x_TotalSavings) / sum(x_CommitmentDiscountSavings) / sum(x_NegotiatedDiscountSavings) which use the canonical row-level clamped calculations

Phase 2 — Future enhancement

4. USD normalization mode — optional toggle to use x_*InUsd columns for cross-currency totals. Blocked until USD column coverage is validated; currently x_*InUsd is set to null for C360 and non-Azure ingestion paths.

Precedent in the repo

The existing Azure Monitor workbooks already handle this correctly:

• Governance workbook scopes cost display per-subscription and shows Currency column
• Optimization workbook groups Advisor savings by currency

The Data Explorer dashboard is the outlier.

Technical notes

• Dashboard uses ADX dashboard schema v60 with base queries (CostsPlus → CostsByMonth/CostsByDay)
• ADX dashboards support query-based parameters that can filter base queries
• Applying the filter in CostsPlus alone would fix the propagation to all downstream queries — minimal blast radius
• x_BilledCostInUsd, x_EffectiveCostInUsd, x_ContractedCostInUsd, x_ListCostInUsd exist in the data model but are not referenced by any dashboard query
• x_BillingExchangeRate and x_BillingExchangeRateDate are carried through the model but not used as guardrails

References

• src/templates/finops-hub/dashboard.json — all 86 queries
• src/templates/finops-hub/modules/Microsoft.FinOpsHubs/Analytics/scripts/HubSetup_v1_2.kql — Costs_v1_2 function (lines 70–310)
• src/templates/finops-hub/modules/Microsoft.FinOpsHubs/Analytics/scripts/IngestionSetup_v1_2.kql — currency column handling, USD normalization, exchange rate logic
• src/workbooks/governance/workbook.json — precedent: displays Currency column
• src/workbooks/optimization/workbook.json — precedent: groups by currency