Wrong grand total

Bishwa

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

ArborRose

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.)

ArborRose

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`))

Bishwa

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

ArborRose

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.)