I need to calculate the average of average Hours spent on audits by Lead Team .
The added caveat that is missing is, don't count any Lead Teams where no time was spent on audits.
I am displaying the team average hours per audit as bars. So if I had a bar1= 50, a bar2= 100, and a bar3= 150, the average would be 100 because the sum of the 3 is 300 divided by 3 gives me an average of 100.
The issue occurs when one of the bars has a zero value . In QlikView that bar can be suppressed (as in Qlik Sense as well) but the QV average reference line automatically takes it out of the equation.
So for example if bar3= 0 and therefore suppressed, QV would sum the 2 non zero bars as 150 and divide by 2 to get an average of 75. In QS, it is still dividing the sum of all 3, 150 by 3 and getting 50.
How do I get QS reference line to behave like QV?
My current expression is:
=Avg(Total Aggr(
Sum({<[Audit Year]={">=2019"}, [Audit Task]={"Reporting"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} Hours)/
Count({<[Audit Year]={">=2019"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} DISTINCT [Audit ID])
, [Lead Team]))
Thanks!
