11 Replies Latest reply on Jul 26, 2018 12:00 AM by Mavis Liu

Calculated fields using Subtotal results

Hi,

I want to create a calculated field using subtotal results.

I basically want to make a calculate field which divides the 2018 subtotal, i.e. 3 with Average of null total, i.e. 52/2.

So(3/(52/2)).

Help appreciated.

Regards,
Deepika

• 1. Re: Calculated fields using Subtotal results

HI Deepika,

I've typed in your data into excel, but please could you provide the dataset for future posts as it'll help us answer your question more efficiently.

I've used this calculation to work out the first part of the calculation (52/2) :

Then I have a division using a table calc:

Then I built the worksheet and added the division field into the view and used last() = 0 to show only the last column:

Thanks,

Mavis

• 2. Re: Calculated fields using Subtotal results

Mavis Liu how did you do the last part:

I don't know how to do it.

Regards,

Deepika

• 3. Re: Calculated fields using Subtotal results

Type ” last () = 0 "

Move to filter shelf and select only true

Shin

• 4. Re: Calculated fields using Subtotal results

Hi Deepika,

Exactly as Shin said, you can double click in the marks card, or in the rows/columns shelf etc. You can then type in the last() =0 table calculation.

Typing in calculations this way is called ad-hoc calculations. This means it's currently only used in the existing worksheet as it's not available in your data pane. You could always drag in the last() = 0 into your data pane to make it accessible to re-use in other worksheets though.

Thanks,

Mavis

• 5. Re: Calculated fields using Subtotal results

Hi Mavis Liu,

My 2018 numbers are not tallying as yours.

Regards,

Deepika

• 6. Re: Calculated fields using Subtotal results

Hi Deepika,

It doesn't look like you're using a table calculation? You've got Cal, but this doesn't seem to be a table calculation. You can tell if it's a table calculation as it should have a delta triangle symbol on the field.

Thanks,

Mavis

• 7. Re: Calculated fields using Subtotal results

Hey Mavis Liu

I got it. Many thanks for the help!

However, I just want to display Division for Total along with just the values for other range/year.

Can you help with that?

Regards,
Deepika

• 8. Re: Calculated fields using Subtotal results

Can this be done as a calculated field instead of table calculation?

• 9. Re: Calculated fields using Subtotal results

Hi Deepika,

Sorry what are you looking for as your end result?

Thanks,

Mavis

• 10. Re: Calculated fields using Subtotal results

The goal is the same. However, table calculation require the values to be in the data pane. So in this case I need the subtotal in the data pane. And I don't want to display it.

• 11. Re: Calculated fields using Subtotal results

Hi Deepika,

Apologies I'm still not quite understanding what you mean. You want the subtotal in the data pane but you don't want to display it?

You can create this table calculation as an ad hoc table calculation for the view if you don't want to see it in the data pane.

Thanks,

Mavis