Wrong grand total

Bishwa
Bishwa Member
edited November 14 in Beast Mode

I have two columns 1) Actual Variance and 2) Absolute Variance.

Actual Absolute

2 2

-2 2

4 4

-3 3

Total 1 11I am getting the correct count 1 for the actual variance but for the absolute variance I am getting the same value as actual with the positive sign.I am using abs function to wrap the actual calculation for the absolute variance.How to derive the correct number for Absolute Variance?Thanks

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    Assuming my direction is correct, you'll need to apply ABS() around your formula. Hard to say without all the details of the formula. You want to calculate the absolute value of the actual variance formula, not just the column, then sum it.

    ABS(Actual Value - Budget Value)

    If your data is grouped, you would need to sum up the variances for each group. And avoid nesting (one inside another). Are you doing this in an ETL or creating a formula on a card? (I prefer to do aggregates on an ETL before taking the data to a card.)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    ArborRose Coach

    Sounds like the issue has to do with abs() being applied to the total sum instead of each individual variance value.

    Calculate Absolute Variance Row by Row: Use the abs() function on each individual value in the "Actual Variance" column to get the absolute values row-by-row.

    Sum the Absolute Values: Once you have the absolute values calculated for each row, sum those values to get the total.

    SUM(ABS(`Actual Variance`))

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Bishwa
    Bishwa Member

    When I try to wrap it around with SUM it says there is a calculation error. I have a formula for Actual Variance it is not straight from the table

  • Bishwa
    Bishwa Member

  • ArborRose
    ArborRose Coach
    Answer ✓

    Assuming my direction is correct, you'll need to apply ABS() around your formula. Hard to say without all the details of the formula. You want to calculate the absolute value of the actual variance formula, not just the column, then sum it.

    ABS(Actual Value - Budget Value)

    If your data is grouped, you would need to sum up the variances for each group. And avoid nesting (one inside another). Are you doing this in an ETL or creating a formula on a card? (I prefer to do aggregates on an ETL before taking the data to a card.)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories