Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Can you share the expression used for Diff and target, I think that is making all the difference.
>>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))
Thanks Jonathan!