Row Total for Percentage of Column
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?
The same question
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:
Please let me know how this works for you.
Regards,
Mike
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:
Please let me know how this works for you.
Regards,
Mike
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
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
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
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
Hi Hannah,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Hannah,
I just wanted to check in and see how things are going with this.
Regards,
Mike
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
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
Replies have been locked on this page!