Help with doing avg totals in table as a KPI Card

[MassicotPSCU]

Hi everyone,

I've seen similar post like this but I'm not sure the solution provided is applicable to my expression. The solution requires set analysis but I'm not sure if or how to use set analysis to filter for nulls.

I have this table: 

I also have this expression:

=if(IsNull(avg([Score])),1,AVG([Score]))

When sales value is blank a score of 1 is returned for the given name. My average in the total score row represents a value of 1.6 which is accurate. However, when I use this same expression in a KPI card I only get the average for the names with a populated sales value. I need the average for all names including the ones without a sales value. 

Is there any way to directly transfer that value in the total's rows to a KPI chart or maybe fix my expression?

Here is a similar post with a solution using set analysis and aggr() but it does not use set analysis to filter for nulls.

Solved: Totals in a table as a KPI - Qlik Community - 1383348

[Chanty4u]

Try this 

=if(IsNull(avg(Total <Name> [Score])), 1, AVG(Total <Name> [Score]))

[MassicotPSCU]

Hi thank you for your response. Unfortunately, I tried this, but I still get back the same incorrect result where it's only averaging the names with scores/sales.

[Kushal_Chawda · Accepted solution]

@MassicotPSCU  try below expression in KPI

=rangesum(Count(distinct{<Name={"=sum(Sales)=0"}>}Name), sum(Score))/ count(distinct Name)

[MassicotPSCU]

Thank you so much @Kushal_Chawda !