How to get KPI visual to exclude Nulls

[Jane]

I have a calculated field of

ifelse(
isNull(avgIf(value, budgetYear = maxOver(budgetYear, [Group], PRE_AGG))) OR isNull(avgIf(value, budgetYear = maxOver(budgetYear, [Group], PRE_AGG) - 1)),
0,
avgIf(value, budgetYear = maxOver(budgetYear, [combinedSearch], PRE_AGG)) / avgIf(value, budgetYear = maxOver(budgetYear, [combinedSearch], PRE_AGG) - 1)
)

This calculated field is to calculate the ratio of (value at the latest year) / (value at the previous year)

However, the final result of table and KPI looks different, even after I exclude Nulls. The first image I uploaded is showing result in table which is 0.94 after successfully exclude Nulls, but the second image is not showing 0.94 in KPI even after adding the filter of excluding Nulls

[andrepgn]

Hi @Jane , welcome back to the Community
Can you please show us a sample of the data used?

Andrea

[Jane]

Hi Andrea,
Sorry for the confusion. The calculated field:

ifelse(
isNull(avgIf(value, budgetYear = maxOver(budgetYear, [Group], PRE_AGG))) OR isNull(avgIf(value, budgetYear = maxOver(budgetYear, [Group], PRE_AGG) - 1)),
0,
avgIf(value, budgetYear = maxOver(budgetYear, [Group], PRE_AGG)) / avgIf(value, budgetYear = maxOver(budgetYear, [Group], PRE_AGG) - 1)
)

With the above calculated field, the calculation for the sample data will be
Group 1 → 2.06
Group 2 → 2
Group 3 → 2
Group 4 → 0
Group 5 → 0

On the KPI visual, it shows (2.06 + 2 + 2) / 5 rather than (2.06 + 2 + 2)/3

However, on the table visual, i am able to exclude NULLs by adding it in the filter; but it does not work in KPI visual

[Jane]

Hi Andrea,

I figured it out.

Thanks for reaching out to help!

[andrepgn]

Hi Jane, good to know
I’ll close the topic, see you soon!
Andrea