12 Replies Latest reply on Jun 14, 2018 10:48 AM by dinesh punnam

# Grand Total of a Column (Calculated Field)

I want to show the total of a column (calculated field) based on other columns.

However, the grand total option does not sum the column value, it shows a calculation based on the sum of other two columns which used in the calculated field:

Original Measures:  Mkt Value, Price

Calculated Field:  Weight of Mkt Value, Composite (Price * Mkt Weight)

Dimension: Tix

I want to show the sum of Composite (price*weight) for all Tix (1~6), it doesn't need to be showed in a table format, any other chart is ok. Attached a sample workbook.

Any help is appreciated!

• ###### 1. Re: Grand Total of a Column (Calculated Field)

Jerry!

You could use LODs instead of table calcs. Create 2 calcs like:

Calculation1:  [Mkt Value]/{sum([Mkt Value])}

Calculation2: [Calculation1]*[Price]

Instead of your original Weight market value and composite calcs: • ###### 2. Re: Grand Total of a Column (Calculated Field)

Pooja, thanks for your solution, again!!

It works perfectly, I don't know why my original cal does not work on grand total, I see my original cal follows your logic but the difference is when I calculate the weight, I used:

SUM([Mkt Value])/TOTAL(sum([Mkt Value])) ,  which is an aggregation,

therefore my Composite cal need to be weight*sum(price), otherwise it will show error like can't mix aggregate and non-aggregate.....

Your solution is great, and simple!

• ###### 3. Re: Grand Total of a Column (Calculated Field)

Pooja, may I ask a FAQ:

Why [Mkt Value] / {sum([Mkt Value])} is working and [Mkt Value] / (sum([Mkt Value])) is not?

I see the only difference is the bracket {}  vs.  (),

you changed () to {}, it works and that calculation is not an aggregate calculation, amazing~

• ###### 4. Re: Grand Total of a Column (Calculated Field)

Jerry!

[Mkt Value] and {sum([Mkt Value])} are both row level whereas (sum([Mkt Value])) is aggregated and hence dividing a row level value by an aggregated value will throw an error of 'can't mix aggs and non-aggs'. LOD indeed requires the use of curly brackets. Hope this helps!

Here is a great resource explaining why your grand totals wouldn't work when using table calcs.

Pooja.

1 of 1 people found this helpful
• ###### 5. Re: Grand Total of a Column (Calculated Field)

Pooja Gandhi

1) One sheet having each  sales office and each bar (is on adv status vs prev year dim )

2) one sheet having all sales office value and each bar ( adv status or prev year dim )

Can we put totals in first sheet split by dimension

Thanks in advance. If I create separate sheets then i cannot share year filter as the table calculation is calculated for specific dimensions in each sheet

• ###### 6. Re: Grand Total of a Column (Calculated Field)

HI Dinesh,

To achieve that(Creating another dimension), you need to duplicate the data.

Here is one example.  But

- Actually your connection is to server and It was difficult to create connection from my side.

- And I think you need to write custom SQL o union your server data.

Other approach is use parameter of "Year" instead.

Thanks,

Shin

• ###### 7. Re: Grand Total of a Column (Calculated Field)

Thanks Shin,

If I use parameter how could I connect the "YEAR L" (lookup(max(year([Transaction_Date])),0))  to parameter ?

The other problem is even though i have two views separately - I cannot connect the year (table calculation to share between two sheets ) - year has to be common filter for those two sheets.

Thanks,

DInesh

• ###### 8. Re: Grand Total of a Column (Calculated Field)

HI Dinesh,

Give me a days or so because it is really trouble some to replicate the connection.

Parameter is absolutely universal and that can be the bridge even across data source, not only the worksheets..

If I hit some difficulties, let you know.

Regards,

Shin

• ###### 9. Re: Grand Total of a Column (Calculated Field)

That would be great ! Thanks so much - I will give it a shot too...

As you know in this scenario , correct me if I am wrong.

1) One data source

2) two table calc

3) Creating parameter and connecting those table calculations to this parameter.

Thanks,

Dinesh

• ###### 10. Re: Grand Total of a Column (Calculated Field)

3) Creating Param ==> creating appropriate filters on respective worksheet to bridge two with using parameter

1 of 1 people found this helpful
• ###### 11. Re: Grand Total of a Column (Calculated Field)

Shin,

I did a simple IF calc and achieved it

IF [Gregorian Year P]=[Year L] THEN [Year L]

END

Year L = lookup(max(year([Transaction_Date])),0)

Thanks guys !!

• ###### 12. Re: Grand Total of a Column (Calculated Field)

All,

The only issue i have right now is to converge separate sheets on totals to one.

Thanks,

Dinesh