SUM(distinct) Fixed By

Viswaja

I have a column that needs to be a constant value based on two more columns. So I am using this formula
SUM(DISTINCT Balance) FIXED (BY Client, Item). This is working fine.
However, When I am using the same formula in another beast mode I am getting the below error

(1-SUM(Lost))/ sum ((SUM(DISTINCT Balance) FIXED (BY Client, Item)) + Received)

An invalid column was specified in your request (the column does not exist on the DataSet). 

However , all the columns exists in the dataset and the beast mode is saving fine but when I use it in any card I am getting the error.

GrantSmith

https://community-forums.domo.com/main/discussion/comment/90770#Comment_90770

The Fixed function is returning a single value for each row and you're adding that to an aggregation across your entire dataset so it doesn't know how to handle this. You'd need to aggregate your fixed function again so that you can add the two together.

SUM(SUM(DISTINCT Balance) FIXED (BY Client, Item)) + SUM(Received)

ArborRose

I had this same thing happen to me last week. It turned out I miss typed single quotes rather than a back tick. I would have suspected order of operation but your parenthesis look right to me.

Remove the space after the second occurrence of sum.

Check column names for spelling and back tick quotes. If your first worked, I would focus on Received and Lost.

Check for hidden characters. To filter properly, copy the whole string to notepad and then copy from notepad back again. Notepad is dumb. It doesn't know how to do an extended or hidden character.

Verify data types.

Viswaja

Thank you for the answer I did check everything but it is still not working for me.

I changed the formula to this and it is showing me a different error now.

(SUM(DISTINCT Balance) FIXED (BY Client, Item)) + SUM(Received)

An issue has occurred during processing. We are unable to complete the request at this time. 
Is there anything I am missing here?

ArborRose

Perhaps there's another way to approach the problem. Can you provide a few sample rows of test values and a description of what you need?

GrantSmith

https://community-forums.domo.com/main/discussion/comment/90770#Comment_90770

The Fixed function is returning a single value for each row and you're adding that to an aggregation across your entire dataset so it doesn't know how to handle this. You'd need to aggregate your fixed function again so that you can add the two together.

SUM(SUM(DISTINCT Balance) FIXED (BY Client, Item)) + SUM(Received)

Viswaja

Thank you both so much.

GrantSmith's fix helped me.