Calculated Field Creates a lot of rows

Mat Chabros shared this question 1 year ago
Answered

Hello!

The attached picture shows my problem, as soon as I am using a Calculated Field the first column is being divided into multiple instances until I aggregate the column where my Calculated Field is.

In the case of a variance in £, this is not a problem, however, the problem is when I try to calculate the percentage difference.

I'd like to use a Calculated Field because I'd like to have calculated total as well, which is not possible with advanced functions.

The first column is a drill-down column, is that causing that issue?

Best wishes,

Mat

Comments (4)

photo
1

Hi Mat,

Thank you for reaching out. If you don't want the Calculated Field divided into multiple instances at all, when you're building your Calculated Field, you can simply click on the Σ symbol to do SUM(fieldname1 - fieldname2), which it looks like in your case would be SUM ( "Month - New" - "Month - Old" ).

929f4b7ecf8a99090fdb0ac443f6600b

Let me know if this is may be what you're looking for.

Thanks,

Mike

photo
1

Hi Mike,

Thank you for taking the time to answer my query. Your method indeed works for subtractions, however, it does not work when I try to calculate the percentage change...

photo
photo
1

Hi Mat,

Thanks for your response. Try dividing your new Calculated Field for variance by SUM(Month - Old):

3216ad9856f986c712b5ce58cf591c0d

As you can see, my variance percentages are correct (.91, and .81). As the equation for variance percentage is: (# under SUM Invoice Estimate - # under SUM Invoiced Amount) / # under SUM Invoiced Amount = .91 (when set to 2 decimal places). In your case it's ultimately "Month - New" - "Month - Old" / "Month - Old". You just have to do it in this roundabout way using two separate calculated fields as exemplified in this and my previous response.

Let me know how this goes.

Thanks,

Mike

photo
1

Hi Mat,

I'm just going through some of my older cases. I'm going to go ahead and mark this one as Answered, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike