10 Replies Latest reply on Sep 17, 2016 9:06 AM by tingting chen

# LOD Calculation Field Problem?

Hi All,

I have used the below code to find the difference between the two fields. This is my requirement.

RUNNING_SUM(AVG([Field1]))-RUNNING_SUM(SUM([Field2]))

I have got all the values as correct except the grand total as shown below snapshot.

Field 1            Field 2              Difference

You can see : Grand total 49691-22327 should be "27364" . But it is in negative which is wrong. Formula is not taking for grand total.

Could someone help me with some code so that Grand total will also get reflected as per the formula?

Best Wishes,

Sandeep

• ###### 1. Re: LOD Calculation Field Problem?

Hi Sandeep,

Tableau's grand total does not simply add column members together.

Jonathan created a great doc about it, and the doc can be found at Why Your Grand Total or Subtotal Isn't Working as Expected

Best Regards,

Tingting

• ###### 2. Re: LOD Calculation Field Problem?

Hi Tingting,

I checked the link which you have mentioned but it doesn't have what I want.

I am using difference of two fields with some functions as mentioned above.To this difference, Grand total is wrong. It is very important for me now.

Best Wishes,

Sandeep

• ###### 3. Re: LOD Calculation Field Problem?

Hi Sandeep,

If we are using running_sum, then the last row excluding the total will be the grand total you are looking for, right? Please correct me if I am wrong.

Also, may you share more about the business case on this particular calculation for us to better understand? Such as what is the meaning of Field 1 and Field 2.

Best Regards,

Tingting

• ###### 4. Re: LOD Calculation Field Problem?

Hi Tingting,

Yes,you are correct.The last row excluding the grand total i am looking for.

Field 1 and Field 2 are numeric or continuous variables.

• ###### 5. Re: LOD Calculation Field Problem?

First thing that confuses is your title "LOD" , where we don't see any lod related issue here.

Here in your requirement , Your grand totals are your last record in the table itself. So please remove the grand totals should be simple enough and easily understandable by people who are reading running sum's.

Thanks & Regards

Srinath. Panjala

• ###### 6. Re: LOD Calculation Field Problem?

Hi Srinath,

I have used Include option so I thought it is related to LOD issue.I cannot remove the Grand total as it is very important to have Grand total in my report.Here is the detail for my output issue.

I have two fields in my data: 'Current Week' , 'PlanValue'

I have created a new field called 'NewValue' as below.The reason to create is this because to get the correct Grand total value.

NewValue = { INCLUDE [Month]:AVG([PlanValue])}.

Now, I want the difference as:

RUNNING_SUM(AVG([NewValue]))-RUNNING_SUM(SUM([Current Week]))... When I use this, All values are correct except the Grand Total value.

Now, I want the correct Grand total value as '24016' not -12879.

Hope I have briefed what I want now.

Thank you.

Best Wishes,

Sandeep

• ###### 7. Re: LOD Calculation Field Problem?

Hi Sandeep,

1 of 1 people found this helpful
• ###### 8. Re: LOD Calculation Field Problem?

Hey Praveen,

That link did not help me as it was throwing some error but it is a helpful link.And I am able to solve using the below code:

{ INCLUDE [Month]:AVG([Field 1])}  -  { INCLUDE [Month]:SUM([Field 2])}

Thanks a lot for all your hep Praveen:)

Best Wishes,

Sandeep

• ###### 9. Re: LOD Calculation Field Problem?

Thanks Tingting. I am able to solve the issue.

Thanks much:)

Best Wishes,
Sandeep

• ###### 10. Re: LOD Calculation Field Problem?

Glad to hear that you have solved the issue on your own

Please mark this question as answered, therefore it will not show in the unsolved question list. Thank you~