ADX Dashboard - Savings/Reservation calculation wrong

[AngelAlves123]

🐛 Problem

In 0.8 Azure Data Explorer Dashboard, Rate Optimizations page, the Reservation savings are not properly calculated, showing a negative number, while utilization of Reservations is 100%.
This is happening with MCA contract type.

👣 Repro steps

1. Go to: Azure Data Explorer 0.8 Dashboard, "Rate optimization" page
2. Scroll down to: Monthly savings trend (as example)
3. Click on: "Type:Reservation:Savings
4. Report shows values in negative amounts

📷 Screenshots

ℹ️ Additional context

Confirmed that "ListCost" field has lower values than "EffectiveCost" for Reservation costs.

🙋‍♀️ Ask for the community

We could use your help:

1. Please vote this issue up (👍) to prioritize it.
2. Leave comments to help us solidify the vision.

[AngelAlves123]

An example directly from Costs_final_v1_0 table:

[flanakin]

Yes, this is a known issue with the data coming from Cost Management. There are a number of reasons this happens. Here's a query we used to identify the different scenarios. Can you share what this returns for you? That will help us know what scenarios you're seeing.

Costs
| extend EffectiveOverContracted = iff(ContractedCost < EffectiveCost, ContractedCost - EffectiveCost, decimal(0))
| extend ContractedOverList      = iff(ListCost < ContractedCost,      ListCost - ContractedCost,      decimal(0))
| extend EffectiveOverList       = iff(ListCost < EffectiveCost,       ListCost - EffectiveCost,       decimal(0))
| extend scenario = case(
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Usage', 'Reservation usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Purchase', 'Reservation purchase missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Usage', 'Savings plan usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Purchase', 'Savings plan purchase missing list',
    ListCost == 0 and ChargeCategory == 'Purchase', 'Other purchase missing list',
    isnotempty(CommitmentDiscountStatus) and ContractedOverList == 0 and EffectiveOverContracted < 0, 'Commitment cost over contracted',
    ListCost == 0 and BilledCost == 0 and EffectiveCost == 0 and ContractedCost > 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost should be 0',
    ListCost == 0 and ContractedCost == 0 and BilledCost > 0 and EffectiveCost > 0 and x_PublisherCategory == 'Vendor' and ChargeCategory == 'Usage', 'Marketplace usage missing list/contracted',
    ContractedOverList < 0 and EffectiveOverContracted == 0 and x_SourceChanges !contains 'MissingListCost', 'ListCost too low',
    ContractedUnitPrice == x_EffectiveUnitPrice and EffectiveOverContracted < 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost doesn''t match price',
    EffectiveOverContracted != 0 and abs(EffectiveOverContracted) < 0.00000001, 'Rounding error',
    ContractedOverList != 0 and abs(ContractedOverList) < 0.00000001, 'Rounding error',
    EffectiveOverList != 0 and abs(EffectiveOverList) < 0.00000001, 'Rounding error',
    ContractedCost < EffectiveCost or ListCost < ContractedCost or ListCost < EffectiveCost, '',
    EffectiveCost <= ContractedCost and ContractedCost <= ListCost, 'Good',
    '')
| project-reorder ListCost, ContractedCost, BilledCost, EffectiveCost, EffectiveOverList, EffectiveOverContracted, ContractedOverList, x_SourceChanges, ListUnitPrice, ContractedUnitPrice, x_BilledUnitPrice, x_EffectiveUnitPrice, CommitmentDiscountStatus, PricingQuantity, PricingUnit, x_PricingBlockSize, x_PricingUnitDescription
| summarize count(), EffectiveOverContracted = sum(EffectiveOverContracted), ContractedOverList = sum(ContractedOverList), EffectiveOverList = sum(EffectiveOverList), Type = arraystring(make_set(x_BillingAccountAgreement)) by scenario

[AngelAlves123]

Here it goes the outcome.

[flanakin]

@AngelAlves123 Looks like what you're seeing is reservations where the effective cost is higher than what you would've paid without the reservation. I checked a few instances of this and the data looks correct. I would suggest contacting support to confirm the prices and costs are correct. Feel free to reference me by name.

Here's a query to get the list of meters:

Costs
| extend EffectiveOverContracted = iff(ContractedCost < EffectiveCost, ContractedCost - EffectiveCost, decimal(0))
| extend ContractedOverList      = iff(ListCost < ContractedCost,      ListCost - ContractedCost,      decimal(0))
| extend EffectiveOverList       = iff(ListCost < EffectiveCost,       ListCost - EffectiveCost,       decimal(0))
| extend Scenario = case(
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Usage', 'Reservation usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Usage' and ChargeCategory == 'Purchase', 'Reservation purchase missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Usage', 'Savings plan usage missing list',
    ListCost == 0 and CommitmentDiscountCategory == 'Spend' and ChargeCategory == 'Purchase', 'Savings plan purchase missing list',
    ListCost == 0 and ChargeCategory == 'Purchase', 'Other purchase missing list',
    isnotempty(CommitmentDiscountStatus) and ContractedOverList == 0 and EffectiveOverContracted < 0, 'Commitment cost over contracted',
    ListCost == 0 and BilledCost == 0 and EffectiveCost == 0 and ContractedCost > 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost should be 0',
    ListCost == 0 and ContractedCost == 0 and BilledCost > 0 and EffectiveCost > 0 and x_PublisherCategory == 'Vendor' and ChargeCategory == 'Usage', 'Marketplace usage missing list/contracted',
    ContractedOverList < 0 and EffectiveOverContracted == 0 and x_SourceChanges !contains 'MissingListCost', 'ListCost too low',
    ContractedUnitPrice == x_EffectiveUnitPrice and EffectiveOverContracted < 0 and x_SourceChanges !contains 'MissingContractedCost', 'ContractedCost doesn''t match price',
    EffectiveOverContracted != 0 and abs(EffectiveOverContracted) < 0.00000001, 'Rounding error',
    ContractedOverList != 0 and abs(ContractedOverList) < 0.00000001, 'Rounding error',
    EffectiveOverList != 0 and abs(EffectiveOverList) < 0.00000001, 'Rounding error',
    ContractedCost < EffectiveCost or ListCost < ContractedCost or ListCost < EffectiveCost, '',
    EffectiveCost <= ContractedCost and ContractedCost <= ListCost, 'Good',
    '')
| project-reorder ListCost, ContractedCost, BilledCost, EffectiveCost, EffectiveOverList, EffectiveOverContracted, ContractedOverList, x_SourceChanges, ListUnitPrice, ContractedUnitPrice, x_BilledUnitPrice, x_EffectiveUnitPrice, CommitmentDiscountStatus, PricingQuantity, PricingUnit, x_PricingBlockSize, x_PricingUnitDescription
| where Scenario == 'Commitment cost over contracted' 
| summarize by ListUnitPrice, ContractedUnitPrice, x_EffectiveUnitPrice, x_PricingBlockSize, x_SkuMeterName, x_SkuMeterId, x_SkuOfferId, SkuPriceId, x_EffectivePeriodStart = BillingPeriodStart, BillingAccountId = tolower(BillingAccountId)