Total row not calculating right

TC1199A

I have a card displays volumes for each of my customers. In some cases, there are duplicate volume numbers, but the total row only calculates one of them. For example, if customer A has a volume of 150 and customer B also has a volume of 150, the total row only calculates customer A.

Any thoughts here?

MichelleH

@TC1199A Be sure that your formula takes the sum of the average and includes a date dimension in the fixed by clause

MichelleH

@TC1199A Do you have any aggregation set on your volume field? It sounds like it may be set to calculate the average rather than the sum.

TC1199A

@MichelleH I am using a beast mode. The beast mode I am using is SUM(DISTINCT Volume). I am using this formula because I need to know the projected volume for each customer for every month. Is there a way to modify this formula so that it counts each distinct volume for every customer even if they have the same projected volume?

MichelleH

@TC1199A I'd recommend using a FIXED function instead like this:

sum(sum(Volume)) FIXED (BY Customer,MONTH(Date))

TC1199A

@MichelleH I have a dataset with the live data and a dataset with the projected data. I am using an SQL to join the two together. The monthly projected value gets assigned to each row of the live data when all constraints are met. This is why I am having to use the distinct function.

MichelleH

@TC1199A That makes sense. FIXED functions are perfect for this type of scenario. Here is a helpful article on how they work with examples: https://domo-support.domo.com/s/article/4408174643607?language=en_US

I realized I had a typo in my original comment. Here is the corrected version:

sum(avg(Volume)) FIXED (BY Customer,MONTH(Date))

TC1199A

@MichelleH I am still having trouble getting it to calculate what I am looking for. Using the formula SUM(SUM(Volume)) FIXED (BY Customer Name) it is still just summing up every fixed value for each row in January.

MichelleH

@TC1199A Be sure that your formula takes the sum of the average and includes a date dimension in the fixed by clause

TC1199A

@MichelleH it is working now. Thank you for your help!