Show zero in table

[ecacarva]

I have a table with Year, Quantity of Employees, Accum Quantity (no dept is selected)

When I select some  department it hides zero values

I selected to include zeros in Complement.

Somebody would help?

[stevedark · Accepted solution]

Hi @ecacarva 

What you are seeing here is not that there is a zero value when the Department is selected, rather there is no association in the data.

The way to deal with this is to ignore the selection, which you can do with set analysis.

Assuming your expression is currently:

sum([Qtde Servidor])

You could add on the end of this the value with selections ignored, multiplied by zero (so it doesn't alter the result):

sum([Qtde Servidor]) + sum({1}[Qtde Servidor]*0)

The {1} in set analysis means ignore all selections and give the value for the whole set.

This will, however, mean that if you select a range of years it will still show all years in the table. This can be resolved using the P() function:

sum([Qtde Servidor]) + sum({1<Ano2=P({$}Ano2)>}[Qtde Servidor]*0)

Alternatively, if it was only the Department selection you wanted to ignore, whilst still respecting all other selections you can do this:

sum([Qtde Servidor]) + sum({<Department=>}[Qtde Servidor]*0)

There is plenty written on-line about set analysis, and why the expressions above do what they do. A quick Google should turn up some explanations.

Hope that helps,

Steve

[ecacarva · Accepted solution]

Thank you @stevedark, it works perfectly with some adaptations !!!

RangeSum(Above(Count(distinct[MATRÍCULA]),0,RowNo())) + sum({1<[%Ano2]=P({$}[%ANO_ADMISSAO])>}[MATRÍCULA]*0)