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

## 🐛 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
7. 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.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

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

🐛 Problem

👣 Repro steps

🤔 Expected

📷 Screenshots

ℹ️ Additional context

🙋‍♀️ Ask for the community

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

Uh oh!

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

Description

🐛 Problem

👣 Repro steps

🤔 Expected

📷 Screenshots

ℹ️ Additional context

🙋‍♀️ Ask for the community

Metadata

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

Issue actions