ADX Dashboard – Rate Optimization: KQL errors across multiple visuals (Commitment discount breakdown, Savings breakdown by month, Effective cost breakdown by month)

[digitalsb]

🐛 Problem

I am using the latest FinOps Toolkit version 12.0 and deployed the hub for the MCA agreement. After deploying the FinOps Toolkit ADX dashboard, several visuals on the Rate Optimization page are failing with KQL errors:

• Commitment discount breakdown (last n months) Error: 'summarize' operator: Failed to resolve scalar expression named 'EffectiveCost'
• Savings breakdown by month Error: Call to iff(): @then data type (decimal) must match the @else data type (real)
• Effective cost breakdown by month Error: Call to iff(): @then data type (decimal) must match the @else data type (real)

👣 Repro steps

1. Go to ADX dashboard for FinOps Toolkit → Rate Optimization page.
2. Ensure the time parameter is set (e.g., let numberOfMonths = int(10);).
3. View the visuals: Commitment discount breakdown (last n months) Savings breakdown by month Effective cost breakdown by month
4. Observe the above errors in each visual.

🤔 Expected

• All visuals render without errors across the selected date range.
• Queries handle empty/missing data cases consistently.

📷 Screenshots

TODO: If applicable, add screenshots to help explain your problem. Remove if not applicable.

ℹ️ Additional context

Affected KQLs behind these visuals

1. Commitment discount breakdown (last n months) KQL:

let numberOfMonths = int(10);
// baseQuery CostsPlus
let CostsPlus = () {
    Costs_v1_2
    //
    // Apply summarization settings
    | where ChargePeriodStart >= monthsago(numberOfMonths)
    | as filteredCosts
    | extend x_ChargeMonth = startofmonth(ChargePeriodStart)
    // TODO: Should we add granularity? -- | extend x_ReportingDate = iff(#"Default Granularity" == 'Monthly'), x_ChargeMonth, startofday(ChargePeriodStart))
    //
    // SKU details
    | extend x_SkuUsageType = tostring(coalesce(SkuPriceDetails.x_UsageType, x_SkuDetails.UsageType))
    | extend x_SkuLicenseUnusedQuantity = x_SkuLicenseQuantity - x_SkuCoreCount
    //
    // Commitment discounts
    | extend x_CommitmentDiscountKey = iff(isempty(x_SkuInstanceType), '', strcat(x_SkuInstanceType, x_SkuMeterId))
    | extend x_SkuTermLabel = case(isempty(x_SkuTerm) or x_SkuTerm <= 0, '', x_SkuTerm < 12, strcat(x_SkuTerm, ' month', iff(x_SkuTerm != 1, 's', '')), strcat(x_SkuTerm / 12, ' year', iff(x_SkuTerm != 12, 's', '')))
    //
    // CSP partners
    // x_PartnerBilledCredit = iff(x_PartnerCreditApplied, BilledCost * x_PartnerCreditRate, todouble(0))
    // x_PartnerEffectiveCredit = iff(x_PartnerCreditApplied, EffectiveCost * x_PartnerCreditRate, todouble(0))
    //
    // Toolkit
    | extend x_ToolkitTool = tostring(Tags['ftk-tool'])
    | extend x_ToolkitVersion = tostring(Tags['ftk-version'])
    | extend tmp_ResourceParent = database('Ingestion').parse_resourceid(Tags['cm-resource-parent'])
    | extend x_ResourceParentId = tostring(tmp_ResourceParent.ResourceId)
    | extend x_ResourceParentName = tostring(tmp_ResourceParent.ResourceName)
    | extend x_ResourceParentType = tostring(tmp_ResourceParent.ResourceType)
    //
    // TODO: Only add differentiators when the name is not unique
    | extend CommitmentDiscountNameUnique = iff(isempty(CommitmentDiscountId), '', strcat(CommitmentDiscountName, ' (', CommitmentDiscountType, ')'))
    | extend ResourceNameUnique           = iff(isempty(ResourceId),           '', strcat(ResourceName,           ' (', ResourceType, ')'))
    | extend x_ResourceGroupNameUnique    = iff(isempty(x_ResourceGroupName),  '', strcat(x_ResourceGroupName,    ' (', SubAccountName, ')'))
    | extend SubAccountNameUnique         = iff(isempty(SubAccountId),         '', strcat(SubAccountName,         ' (', split(SubAccountId, '/')[3], ')'))
    //
    // Explain why cost is 0
    | extend x_FreeReason = case(
        BilledCost != 0.0 or EffectiveCost != 0.0, '',
        PricingCategory == 'Committed', strcat('Unknown ', CommitmentDiscountStatus, ' Commitment'),
        x_BilledUnitPrice == 0.0 and x_EffectiveUnitPrice == 0.0 and ContractedUnitPrice == 0.0 and ListUnitPrice == 0.0 and isempty(CommitmentDiscountType), case(
            x_SkuDescription contains 'Trial', 'Trial',
            x_SkuDescription contains 'Preview', 'Preview',
            'Other'
        ),
        x_BilledUnitPrice > 0.0 or x_EffectiveUnitPrice > 0.0, case(
            PricingQuantity > 0.0, 'Low Usage',
            PricingQuantity == 0.0, 'No Usage',
            'Unknown Negative Quantity'
        ),
        'Unknown'
    )
    //
    | extend x_ResourceTop1K = ChargeCategory != 'Usage' or isempty(ResourceId) or ResourceId in (
        filteredCosts
        | where isnotempty(ResourceId) and ChargeCategory == 'Usage'
        | summarize sum(EffectiveCost) by ResourceId
        | order by sum_EffectiveCost desc
        | limit 1000
        | distinct ResourceId
    )
    //
    | project-away tmp_ResourceParent
};
// baseQuery CostsByMonth
let CostsByMonth = () {
    CostsPlus
    | where startofmonth(ChargePeriodStart) >= startofmonth(now(), -numberOfMonths)
    | extend ChargePeriodStart = startofmonth(ChargePeriodStart)
    | extend BillingPeriodStart = startofmonth(BillingPeriodStart)
};
let data = materialize(
    CostsByMonth
    //
    | where isnotempty(CommitmentDiscountStatus)
    //
    // Guarantee there's a row for every combination
    | union (
        print json = dynamic([
            {"order": 11, "CommitmentDiscountType": "Reservation", "CommitmentDiscountStatus": "Used"},
            {"order": 12, "CommitmentDiscountType": "Reservation", "CommitmentDiscountStatus": "Unused"},
            {"order": 21, "CommitmentDiscountType": "Savings Plan", "CommitmentDiscountStatus": "Used"},
            {"order": 22, "CommitmentDiscountType": "Savings Plan", "CommitmentDiscountStatus": "Unused"}
        ])
        | mv-expand json
        | evaluate bag_unpack(json)
        | extend EffectiveCost = todecimal(0)
    )
    //
    | summarize Value = sum(EffectiveCost), order = sum(order) by CommitmentDiscountStatus, CommitmentDiscountType
    | order by order asc
    | project Label = strcat(CommitmentDiscountStatus, ' ', tolower(CommitmentDiscountType), 's'), Value
);
data

2. Savings breakdown by month KQL:

let numberOfMonths = int(10);
// baseQuery CostsPlus
let CostsPlus = () {
    Costs_v1_2
    //
    // Apply summarization settings
    | where ChargePeriodStart >= monthsago(numberOfMonths)
    | as filteredCosts
    | extend x_ChargeMonth = startofmonth(ChargePeriodStart)
    // TODO: Should we add granularity? -- | extend x_ReportingDate = iff(#"Default Granularity" == 'Monthly'), x_ChargeMonth, startofday(ChargePeriodStart))
    //
    // SKU details
    | extend x_SkuUsageType = tostring(coalesce(SkuPriceDetails.x_UsageType, x_SkuDetails.UsageType))
    | extend x_SkuLicenseUnusedQuantity = x_SkuLicenseQuantity - x_SkuCoreCount
    //
    // Commitment discounts
    | extend x_CommitmentDiscountKey = iff(isempty(x_SkuInstanceType), '', strcat(x_SkuInstanceType, x_SkuMeterId))
    | extend x_SkuTermLabel = case(isempty(x_SkuTerm) or x_SkuTerm <= 0, '', x_SkuTerm < 12, strcat(x_SkuTerm, ' month', iff(x_SkuTerm != 1, 's', '')), strcat(x_SkuTerm / 12, ' year', iff(x_SkuTerm != 12, 's', '')))
    //
    // CSP partners
    // x_PartnerBilledCredit = iff(x_PartnerCreditApplied, BilledCost * x_PartnerCreditRate, todouble(0))
    // x_PartnerEffectiveCredit = iff(x_PartnerCreditApplied, EffectiveCost * x_PartnerCreditRate, todouble(0))
    //
    // Toolkit
    | extend x_ToolkitTool = tostring(Tags['ftk-tool'])
    | extend x_ToolkitVersion = tostring(Tags['ftk-version'])
    | extend tmp_ResourceParent = database('Ingestion').parse_resourceid(Tags['cm-resource-parent'])
    | extend x_ResourceParentId = tostring(tmp_ResourceParent.ResourceId)
    | extend x_ResourceParentName = tostring(tmp_ResourceParent.ResourceName)
    | extend x_ResourceParentType = tostring(tmp_ResourceParent.ResourceType)
    //
    // TODO: Only add differentiators when the name is not unique
    | extend CommitmentDiscountNameUnique = iff(isempty(CommitmentDiscountId), '', strcat(CommitmentDiscountName, ' (', CommitmentDiscountType, ')'))
    | extend ResourceNameUnique           = iff(isempty(ResourceId),           '', strcat(ResourceName,           ' (', ResourceType, ')'))
    | extend x_ResourceGroupNameUnique    = iff(isempty(x_ResourceGroupName),  '', strcat(x_ResourceGroupName,    ' (', SubAccountName, ')'))
    | extend SubAccountNameUnique         = iff(isempty(SubAccountId),         '', strcat(SubAccountName,         ' (', split(SubAccountId, '/')[3], ')'))
    //
    // Explain why cost is 0
    | extend x_FreeReason = case(
        BilledCost != 0.0 or EffectiveCost != 0.0, '',
        PricingCategory == 'Committed', strcat('Unknown ', CommitmentDiscountStatus, ' Commitment'),
        x_BilledUnitPrice == 0.0 and x_EffectiveUnitPrice == 0.0 and ContractedUnitPrice == 0.0 and ListUnitPrice == 0.0 and isempty(CommitmentDiscountType), case(
            x_SkuDescription contains 'Trial', 'Trial',
            x_SkuDescription contains 'Preview', 'Preview',
            'Other'
        ),
        x_BilledUnitPrice > 0.0 or x_EffectiveUnitPrice > 0.0, case(
            PricingQuantity > 0.0, 'Low Usage',
            PricingQuantity == 0.0, 'No Usage',
            'Unknown Negative Quantity'
        ),
        'Unknown'
    )
    //
    | extend x_ResourceTop1K = ChargeCategory != 'Usage' or isempty(ResourceId) or ResourceId in (
        filteredCosts
        | where isnotempty(ResourceId) and ChargeCategory == 'Usage'
        | summarize sum(EffectiveCost) by ResourceId
        | order by sum_EffectiveCost desc
        | limit 1000
        | distinct ResourceId
    )
    //
    | project-away tmp_ResourceParent
};
// baseQuery CostsByMonth
let CostsByMonth = () {
    CostsPlus
    | where startofmonth(ChargePeriodStart) >= startofmonth(now(), -numberOfMonths)
    | extend ChargePeriodStart = startofmonth(ChargePeriodStart)
    | extend BillingPeriodStart = startofmonth(BillingPeriodStart)
};
CostsByMonth
| extend x_AmortizationClass = case(
    ChargeCategory == 'Purchase' and isnotempty(CommitmentDiscountCategory), 'Principal',
    isnotempty(CommitmentDiscountCategory), 'Amortized Charge',
    ''
)
| extend x_CommitmentDiscountSavings = iff(ContractedCost == 0,      decimal(0), ContractedCost - EffectiveCost)
| extend x_NegotiatedDiscountSavings = iff(ListCost == 0,            decimal(0), ListCost - ContractedCost)
| extend x_TotalSavings              = iff(ListCost == 0,            decimal(0), ListCost - EffectiveCost)
| summarize
    ['List cost']      = round(sumif(ListCost, x_AmortizationClass != 'Principal'), 2),
    ['Effective cost'] = round(sum(EffectiveCost), 2),
    Savings            = round(sum(x_TotalSavings), 2)
    by
    Account = x_BillingProfileId,
    Month   = substring(startofmonth(ChargePeriodStart), 0, 7)
| extend ESR = percentstring(Savings/ ['List cost'])
| order by Month desc

3. Effective cost breakdown by month KQL

let numberOfMonths = int(10);
// baseQuery CostsPlus
let CostsPlus = () {
    Costs_v1_2
    //
    // Apply summarization settings
    | where ChargePeriodStart >= monthsago(numberOfMonths)
    | as filteredCosts
    | extend x_ChargeMonth = startofmonth(ChargePeriodStart)
    // TODO: Should we add granularity? -- | extend x_ReportingDate = iff(#"Default Granularity" == 'Monthly'), x_ChargeMonth, startofday(ChargePeriodStart))
    //
    // SKU details
    | extend x_SkuUsageType = tostring(coalesce(SkuPriceDetails.x_UsageType, x_SkuDetails.UsageType))
    | extend x_SkuLicenseUnusedQuantity = x_SkuLicenseQuantity - x_SkuCoreCount
    //
    // Commitment discounts
    | extend x_CommitmentDiscountKey = iff(isempty(x_SkuInstanceType), '', strcat(x_SkuInstanceType, x_SkuMeterId))
    | extend x_SkuTermLabel = case(isempty(x_SkuTerm) or x_SkuTerm <= 0, '', x_SkuTerm < 12, strcat(x_SkuTerm, ' month', iff(x_SkuTerm != 1, 's', '')), strcat(x_SkuTerm / 12, ' year', iff(x_SkuTerm != 12, 's', '')))
    //
    // CSP partners
    // x_PartnerBilledCredit = iff(x_PartnerCreditApplied, BilledCost * x_PartnerCreditRate, todouble(0))
    // x_PartnerEffectiveCredit = iff(x_PartnerCreditApplied, EffectiveCost * x_PartnerCreditRate, todouble(0))
    //
    // Toolkit
    | extend x_ToolkitTool = tostring(Tags['ftk-tool'])
    | extend x_ToolkitVersion = tostring(Tags['ftk-version'])
    | extend tmp_ResourceParent = database('Ingestion').parse_resourceid(Tags['cm-resource-parent'])
    | extend x_ResourceParentId = tostring(tmp_ResourceParent.ResourceId)
    | extend x_ResourceParentName = tostring(tmp_ResourceParent.ResourceName)
    | extend x_ResourceParentType = tostring(tmp_ResourceParent.ResourceType)
    //
    // TODO: Only add differentiators when the name is not unique
    | extend CommitmentDiscountNameUnique = iff(isempty(CommitmentDiscountId), '', strcat(CommitmentDiscountName, ' (', CommitmentDiscountType, ')'))
    | extend ResourceNameUnique           = iff(isempty(ResourceId),           '', strcat(ResourceName,           ' (', ResourceType, ')'))
    | extend x_ResourceGroupNameUnique    = iff(isempty(x_ResourceGroupName),  '', strcat(x_ResourceGroupName,    ' (', SubAccountName, ')'))
    | extend SubAccountNameUnique         = iff(isempty(SubAccountId),         '', strcat(SubAccountName,         ' (', split(SubAccountId, '/')[3], ')'))
    //
    // Explain why cost is 0
    | extend x_FreeReason = case(
        BilledCost != 0.0 or EffectiveCost != 0.0, '',
        PricingCategory == 'Committed', strcat('Unknown ', CommitmentDiscountStatus, ' Commitment'),
        x_BilledUnitPrice == 0.0 and x_EffectiveUnitPrice == 0.0 and ContractedUnitPrice == 0.0 and ListUnitPrice == 0.0 and isempty(CommitmentDiscountType), case(
            x_SkuDescription contains 'Trial', 'Trial',
            x_SkuDescription contains 'Preview', 'Preview',
            'Other'
        ),
        x_BilledUnitPrice > 0.0 or x_EffectiveUnitPrice > 0.0, case(
            PricingQuantity > 0.0, 'Low Usage',
            PricingQuantity == 0.0, 'No Usage',
            'Unknown Negative Quantity'
        ),
        'Unknown'
    )
    //
    | extend x_ResourceTop1K = ChargeCategory != 'Usage' or isempty(ResourceId) or ResourceId in (
        filteredCosts
        | where isnotempty(ResourceId) and ChargeCategory == 'Usage'
        | summarize sum(EffectiveCost) by ResourceId
        | order by sum_EffectiveCost desc
        | limit 1000
        | distinct ResourceId
    )
    //
    | project-away tmp_ResourceParent
};
// baseQuery CostsByMonth
let CostsByMonth = () {
    CostsPlus
    | where startofmonth(ChargePeriodStart) >= startofmonth(now(), -numberOfMonths)
    | extend ChargePeriodStart = startofmonth(ChargePeriodStart)
    | extend BillingPeriodStart = startofmonth(BillingPeriodStart)
};
CostsByMonth
| extend x_AmortizationClass = case(
    ChargeCategory == 'Purchase' and isnotempty(CommitmentDiscountCategory), 'Principal',
    isnotempty(CommitmentDiscountCategory), 'Amortized Charge',
    ''
)
| extend x_CommitmentDiscountSavings = iff(ContractedCost == 0,      decimal(0), ContractedCost - EffectiveCost)
| extend x_NegotiatedDiscountSavings = iff(ListCost == 0,            decimal(0), ListCost - ContractedCost)
| extend x_TotalSavings              = iff(ListCost == 0,            decimal(0), ListCost - EffectiveCost)
| summarize
    ['On-demand']    = round(sumif(EffectiveCost, PricingCategory == 'Standard'), 2),
    Spot             = round(sumif(EffectiveCost, PricingCategory == 'Dynamic'), 2),
    Reservation      = round(sumif(EffectiveCost, CommitmentDiscountType == 'Reservation'), 2),
    ['Savings plan'] = round(sumif(EffectiveCost, CommitmentDiscountType == 'Savings Plan'), 2),
    ['Other']        = round(sumif(EffectiveCost, PricingCategory !in ('Standard', 'Dynamic') and isempty(CommitmentDiscountType)), 2)
    by
    Account = x_BillingProfileId,
    Month   = substring(startofmonth(ChargePeriodStart), 0, 7)
| order by Month desc

🙋‍♀️ 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.

[helderpinto]

Hi, @santoshblearner! Thank you for reporting this issue. Before we publish a fix for it, can you please follow the manual steps below and confirm if it solves the problems?

Select the Rate Optimization page and switch to Edit mode.

For each failing tile, "Edit" it and search&replace every "decimal" string by "real". At the end, apply changes.

After fixing the 4 different failing tiles, just Save the dashboard

Please, let me know how it goes.

[digitalsb]

Thanks @helderpinto for the fix.

I updated all three KQL queries by changing the data type from decimal to real, and everything is working now:

1. Commitment discount breakdown (last n months): changed todecimal(0) to toreal(0)
2. Savings breakdown by month: changed decimal(0) to real(0)
3. Effective cost breakdown by month: changed decimal(0) to real(0)

Additionally, there were a few other visuals in the ADX dashboard that referenced decimal instead of real. I corrected those as well.

[helderpinto]

Thanks for confirming, @santoshblearner. I am adding @MSBrett for visibility and for making sure we have this fixed in the next version.