Dollar Sign Expansion not working properly

[lucasdavis500]

I'm very confused. I have a field that contains a list of names of employees that have interacted with an account.

example data:

account          analyst

123                     Joe

234                     Kim

I use a variable to store the names of the team that I work with in a variable expression, and use that to match accounts they've been tied to, using IF(MATCH())

This work fine.

I have an issue when trying to utilize this in an expression. For example, I'm trying to find the average of the accounts they are tied to in any given month.

My formula looks like

((SUM(Var1Dummy)) + (SUM(Var2Dummy))) /

(Count(Distinct($(vTeam))))

The reason I'm confused is because this formula does NOT return the correct number for average. If i multiple it back by the number of people on the team, it's greater than 100% of the actual number for that month.

When I use  (Count(Distinct($(vTeam))))  In a Text Object - It returns 14, the number of analysts on the team.

When I hardcode '14' in the formula, it seems to return the appropriate number for the monthly average. I just don't want to have to hardcode the amount of analysts on the team. I would rather it change when the variables is updated with any analyst who has joined/left the team.

I'm confused as to how this SAME formula is resolving in a text object appropriately, 14, but not return the appropriate amount when used in the formula. Also, I've wrapped the numerator and denominator in () to ensure they are resolving correctly.

[marcus_sommer · Accepted solution]

I think you need to ignore the dimensions of the chart. Try it with: Count(Distinct TOTAL($(vTeam)) or maybe putting these calculations into a variable, too and using them this variable within your expression.

- Marcus

[amirkachlon]

In the expression vTeam , add  '=' at the beginning.

And the calculation will be calculated globally once and will give you the desired value