Why is this expression in a pivot table ignoring the TOTAL by dimension value?...

[diagonjope]

Greetings,

I hope someone can suggest a solution for this issue that looks pretty simple, but is giving me a headache. 

I need to have a pivot table with two Expression columns: the first is the avg of the values of one field based on one of the dimensions in the table, and the other is the count of values below the avg.   The avg column works fine, but the number of values below it for each dimension value is incorrect, because the expression is taking the overall avg for the comparison.  I have tried using both a regular sum() with an internal if comparison and a set analysis approach, but both fail to produce the correct values:

sum( aggr( sum( if(FOB < avg(TOTAL FOB),  1)  ), Pais))

sum(aggr( count({$<FOB={"<$(=avg(TOTAL<Pais> FOB))"}>} FOB), Pais))

I've already tried with using Pais as qualifier in the TOTAL, as in  Total<Pais>, but it doesn't seem to work.  My understanding is that the set expression doesn't work, because the value of avg() is calculated before the actual calculation by row is carried out, so the value used is the overall avg of all values of Pais.

As an example, the image below shows incorrect values of Conteo for ESPAÑA and HONDURAS.  The correct values for each of these values of Pais are only shown when one selects one of the dimension values independently.  For instance, check what happens to the value of "Conteo" when one selects the value of "ESPAÑA" in the Pais dimension:

I am attaching the test QVF in case anyone wants to take a look and suggest alternatives.

@GabrielAraya @pablolabbe : do you have any suggestions?

Please advise.

Cheers,

++José