Row Total for Percentage of Column

Hannah Combs shared this question 48 days ago
Answered

Hello,


In this report I have a column that is an advanced function (percentage of column), and my options for totaling the column are to sum or average it. What I would actually like the total to do is create the % variance (in this case 3,888/36,011). Is that possible?

/676dfd628d1ac391b0b0b78fe497d08e

Comments (5)

photo
1

Hi Hannah,

Thanks for reaching out. So basically, the entire column is Column A divided by Column C, correct?

As such, you can accomplish this by creating a Calculated Field and doing

SUM (FP Current Baseline) / SUM (FP Current Baseline Cost Variance)

Then selecting Calculated Total from the Totals section (as opposed to SUM or AVG).

As you can see here, each Columns values in my example below, including the total, is correct:

/d3570a7598bacbb9d55d9afd183478a6

/63aa35d79873e70dfe88efb17a85c44b

/f2ffbd0fe4493ed5e8a7750ed0ef068e

Please let me know how this works for you.

Regards,

Mike

photo
1

Hi Mike,

I've tried this solution - the only way I can get accurate percentages in my data is to use the advanced function option. Is there a way to do a calculated total with an advanced function column?

Hannah

photo
1

Hi Hannah,

You technically can use Calculated Totals with Advanced Functions, but it won't give you the result you're looking for. For example, you can create a Calculated Field that's just 'SUM (Invoice Estimate)', then apply the Compare Against Column Advanced Function to it, at which point you'll still have the Calculated Total option, but the Calculated Total here will just be doing the SUM of Invoice Estimate as the Total, instead of Amount / Estimate as per my previous example since that's what the calculation is.

The question at this point is why would the Calculated Field be incorrect? I used SUM (field1) / SUM (field2) as an example for the CF, but you should actually be matching the aggregation applies to each field. I can't see what aggregations you have applied to your FP Current Baseline and FP Current Baseline Cost Variance fields, but if one of them is aggregated as an AVG field for example, then it'd instead by SUM (field1) / AVG (field2). I'd think it should be possible to get your desired results via Calculated Fields. Have you matched your aggregations on each field?

Regards,

Mike

photo
1

Hi Hannah,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Hi Hannah,

I've been informed by Joel this has been resolved, so I'm going to go ahead and close this ticket out.

Please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike