# Grand total for calculated values is wrong asked on December 3, 2020

Hi support team,

We are using a simple if-clause similar to

`if( sum('Quantity') > 20 , sum('Price'), sum('Price') * 2 )`

, but the grand total calculation is wrong.

Grand Total should be 234, but it is 204
Is this something that could be addressed by changing the formula?

Thanks,

Rainer

Public December 3, 2020

Hello, Rainer,

Thank you for contacting us.

Our team would like to explain that the grand total of the calculated measure is calculated using the formula as well. It means it is based on the grand totals of the “Quantity” and “Price” fields.

If you want to see the sum of previously calculated values, we suggest enabling the `individual` property:

```{  ...  formula: "if(sum('Quantity') > 20, sum('Price'), sum('Price') * 2)",  individual: true}```
``````

We modified the provided JSFiddle to demonstrate this approach: https://jsfiddle.net/flexmonster/pwn4tk84/.

You are welcome to see our documentation dedicated to calculated values to learn more about the `individual` property: https://www.flexmonster.com/doc/calculated-values/.

Please let us know if it works for you.

Kind regards,
Illia

Public December 3, 2020

Hi Illia,

thanks for answering. I tried the suggested solution using the individual prop, but that doesn’t create the desired result.
Here’s the fiddle https://jsfiddle.net/50aL7xvs/8 that demonstrates the issue.
With `individual: false` the values in the Calculated field are correct. However, Grand Total is not.
With `individual: true` the values in the Calculated field are wrong.

The provided GrandTotal attachment shows the result with `individual: false`, which is closer to what we’re trying to accomplish. In that case, only the Grand Total must be adjusted.

Thanks,
Rainer

Attachments:
GrandTotal.png

Public December 4, 2020

Hi, flexmonster team,

Any update on this one? I’d like to understand if this is an issue with the format of the demo data, the way we configure flexmonster, or if this is a known flexmonster limitation.

Kind regards,

Rainer

Public December 4, 2020

Hello, Rainer,

We want to confirm that `individual: true` affects the way all values of the measure are calculated.

Currently, this property is the only way to affect the grand total calculation of the calculated value.

Best regards,
Illia

Public December 4, 2020

Hi Illia,
My question is not about Individual: true|false property, it’s how we can create the correct grand total calculation based on the sample data that I’ve provided.
Please let me know if this a problem with the test data.
If not and it’s a known flexmonster limitation, then please let me know what would be necessary to address it. I could then forward the information to our flexmonster contact person.
Kind regards,
Rainer

Public December 7, 2020

Hello, Rainer,

Thank you for the feedback.

Currently, the specified formula defines how to calculate the grand total of the calculated measure. It means the grand total is still based on measures used in the formula.

For example, the mentioned JSFiddle uses the following formula: `"if(sum('Quantity') > 20, sum('Price'), sum('Price') * 2)"`.
It means the grand total of the “Formula #1” measure is calculated as shown below:
If the grand total of the “Quantity” measure is greater than 20, then the grand total of the “Price” measure, else the grand total of the “Price” measure multiplied by two: `if(41 > 20, 204, 204 * 2) = 204`.
This is the default behavior of the grand total for calculated values.

Currently, the only way to change this behavior is to set the `individual` property to `true`.
It allows displaying the summarized value (`174 + 60 = 234`) instead of using the formula. However, this property affects the way all values are calculated.

We agree that the possibility to change the grand total calculation logic sounds reasonable.
Currently, the corresponding task is added to our backlog. Yet, our roadmap is full at the moment, therefore we are not ready to provide you with an exact ETA for this feature.

Our team will keep you updated on this point.

Regards,
Illia

Public December 7, 2020

Hi Illia,
The mentioned fiddle isn’t the latest in our thread, please take a look at this one instead: https://jsfiddle.net/50aL7xvs/8

The formula that’s used there is simple. If there’s a sum(“Val1”) > 0 take it otherwise take values from  sum(“Val2”)

`if( sum("Val1") > 0 , sum("Val1") , sum("Val2")  )`

What you described seems to be perfectly covered by the default behavior of the grand total for calculated values. The formula produces the correct result at the row level, however, it doesn’t at the grand total level.

Within the attached file you can see that in case the sum(“Val1”) > 0 the value is correctly taken from sum(“Val2”). However, the grand total only takes sum(“Val1”) values into account.
Kind regards,
Rainer

Attachments:
GrandTotal.png

Public December 8, 2020

Hello, Rainer,

For the calculated measures, it is expected to have grand totals calculated according to the formula in our current implementation.

On the provided screenshot, the same formula is used to calculate the grand total:

``````if(sum("Val1") > 0, sum("Val1"), sum("Val2"))
``````

When calculating the grand total, `sum("Val1")` equals 9999, and `sum("Val2")` equals 115050.
It means the result will be calculated as shown below:

``````if(9999 > 0, 9999, 115050) = 9999
``````

Could you please clarify that summing of calculated row values is a preferable behavior for grand totals for your case?
Our team is looking forward to hearing from you.

Regards,
Illia

Public December 8, 2020

Hi Illia,
I can see now where the disconnection is.
We would expect the grand total to be the sum of the calculated row values not a calculation in itself. Please let us know if this can be accomplished using flexmonster.
Please see the attached Excel files for details.
Kind regards,
Rainer

Public December 9, 2020

Hello, Rainer,

Thank you for the detailed information about the desired results.

Currently, the default behavior of the grand total for calculated values cannot be changed.

Yet, the possibility to calculate the sum of rows in the grand total sounds reasonable.