Calculating total in a straight table

[dafnis14]

Hi,

I'm trying to achieve the following:

I have a metric: Target = Quantity - Diff

In a straight table,  I would like to display one row for a country, which would be identical

to the Target displayed in the top table (Straight Table - with Branches), which is based on the sum of rows

But, I receive total which is calculated as the difference of the total sums instead.

How can this calculation be achieved?

Straight Table - with Branches
Branch	Quantity	Diff	Target
	5311	2903	4608
A	1739	274	1465
B	1595	252	1343
C	1977	177	1800
Straight Table - with Branches - Wanted
Country	Quantity	Diff	Target
France	5311	2903	4608
Straight Table - with Branches - Received
Country	Quantity	Diff	Target
France	5311	2903	2408

Thanks!

[Digvijay_Singh]

Can you share the expression used for Diff and target, I think that is making all the difference.

[jonathandienst · Accepted solution]

>>The wrong Diff total in the first table was received because he expression's Total Mode was defined as the expression Total.

>> When changing to Sum of rows, it would display correct.

A pivot table always calculates the expression total and not a sum of rows. To get sum of rows in a pivot, you need to use a Sum(Aggr(.....)) expression, using this syntax:

     =Sum(Aggr( ... your expression here ..., <chart dimension1>, <chart dimension 2>, ...)

The chart dimensions are ALL the fields used as chart dimensions and they must all be included in the Aggr()

dimensions. Example:

     =Sum(Aggr(Sum(Amount), Country, Branch))

[dafnis14]

Thanks Jonathan!