6 Replies Latest reply on Nov 21, 2018 5:18 AM by Norbert Maijoor

# Multiple Calculations On A Subtotal Line

Hi all,

Please help as I'm tearing my hair out to correct this issue. I have a dashboard which will show target, achieved, no of accounts and accounts renewed. In addition to these 4, I want to include the % of achieved to target and renewed accounts to total.

As you can see in Excel it shows as below

but when I do the same in Tableau having dragged from the Analytics tab the totals to subtotals I get the following

As you can see the % columns have summed up rather than following the calculation down. I have tried using LOD calculations to assist but it doesn't work. It needs to all be on the same table. I have worked so hard to get Tableau in my company and I really need your help to show that Excel doesn't win hands down on a basic element of analysis. I have attached a 2018.1 packaged workbook but I can add it in lower versions if needed.

Thanking you all in advance as I have literally been kept awake at night trying to figure this out.

Roger

• ###### 1. Re: Multiple Calculations On A Subtotal Line

Hi Roger,

Did you already evaluate the following option?

Regards,

Norbert

• ###### 2. Re: Multiple Calculations On A Subtotal Line

Hi Norbert,

I did look at this option however as the % is still incorrect and I need a sum of the other columns rather than an average, it wasn't going to work :-(

Ideally it would be sum the first 2 columns, calculate the Total % of Rev Ach, sum the next 2 columns and then calculate the % of accounts renewed.

Thanks

Roger

• ###### 3. Re: Multiple Calculations On A Subtotal Line

Hi Roger,

Find my updated approach as reference below and stored in attached workbook version 10.2 located in the original thread.

1. M1. Base Sum:

if size()<>1 then sum([Base])

elseif size()=1 then sum([Base])

END

2. % Rev Ach > right click > text menu > Mearure > Average

Hope it helps,

Regards,

Norbert

2 of 2 people found this helpful
• ###### 4. Re: Multiple Calculations On A Subtotal Line

Hi Norbert,

You are a legend. That has worked as can be seen below

You can't believe how much I appreciate your help on this. I have re-attached the document with the inclusion of the extra columns

• ###### 5. Re: Multiple Calculations On A Subtotal Line

Hi Roger,

Regards,

Norbert

• ###### 6. Re: Multiple Calculations On A Subtotal Line

Hi Roger,

As said: Much appreciated:))

Regards,

Norbert