This is the logic for the calculated field:

distinctCountOver({TEAM_NAME}, [{X-Axis Grouping}, Ratio], PRE_AGG) / distinctCountOver({RESOURCE_NAME}, [{X-Axis Grouping}, Ratio], PRE_AGG)

When I create the calculated field I am getting no errors, but when I try to display the calculated field in a visual I am getting a syntax error.

I found a similar question and answer here (Distinctcountover not taking calculative field as partition by field - #2 by Jesse), although the solution presented does not apply to my use case, as the field I am trying to partition by (âRatioâ) is a calculated field based on a parameter in the analysis.

Hi @guhifdgh - I believe the distinctCountOver provides multiple values depend upon the group by and this is why the error is coming when you are doing the division. Can you please provide the sample input and expected output and check your problem statement.

By saying that letâs hear from other experts @Biswajit_1993 @duncan @David_Wong

Regards - Sanjeeb

1 Like

Hi @guhifdgh,

Can you show how youâre calculating Ratio and the error message youâre getting?

Is it possible for distinctCountOver({RESOURCE_NAME}, [{X-Axis Grouping}, Ratio], PRE_AGG) to be 0?

1 Like

Thanks for the reply @David_Wong - the calculated field âRatioâ is calculated as follows:

âifelse(${RatioDesired} = âJob Level Distributionâ, {JOB_LEVEL}, ${RatioDesired} = 'âJob Familyâ, {JOB_FAMILY},${RatioDesired} = âJob Family Groupâ, {JOB_FAMILY_GROUP}, ${RatioDesired} = âAssociate vs Non-Associateâ, {ASSOCIATE_TYPE},

${RatioDesired} = âProduct/Tech/Designâ, {Product/Tech/Design}, {ROLE_NAME})â where âRatioDesiredâ is a parameter that the user selects from a dropdown within the dashboard.

It is possible for distinctCountOver({RESOURCE_NAME}, [{X-Axis Grouping}, Ratio], PRE_AGG) to be 0, could that be what the problem is related to?

What happens if you try to add the numerator and denominator separately to a visual? Does QuickSight give you the same error?

If I separate out the numerator and denominator it gives me the same error in both cases.

âYour calculated field expression contains invalid syntax. Correct the syntax and try againâ Show details â source error code: DISTINCTCOUNTOVER_INVALID_ARGUMENT

I tried to create similar calculated fields in my analysis and didnât get any errors.

Is {X-Axis Grouping} a calculated field? If so, can you show the calculation?

Thanks David - I was able to get the calculation working