Total row not calculating right

Options
TC1199A
TC1199A Member
in Dashboards

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?

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

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

Answers

  • MichelleH
    MichelleH Coach

    @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
    TC1199A Member

    @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
    MichelleH Coach

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

    sum(sum(Volume)) FIXED (BY Customer,MONTH(Date))
  • TC1199A
    TC1199A Member

    @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
    MichelleH Coach
    edited April 2024

    @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
    TC1199A Member

    @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
    MichelleH Coach
    Answer ✓

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

  • TC1199A
    TC1199A Member

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

Categories