Key Figure divided by the Overall Result

[Former Member]

Hi Experts,

I need help on aggregations or calculations setting of formula variables for this sample report.

Region	Region1		Region 2		Region 3		Region 4		Region 5		Overall Result
Material	Quantity	%	Quantity	%	Quantity	%	Quantity	%	Quantity	Percentage	Quantity
1	1	0.01	2	0.2	3	0.3	4	0.2	5	0.2	15
2	1	0.2	2	0.2	3	0.3	4	0.2	5	0.2	15
3	1	0.2	2	0.2	3	0.3	4	0.2	5	0.2	15
4	1	0.2	2	0.2	3	0.3	4	0.2	5	0.2	15
5	1	0.2	2	0.2	3	0.3	4	0.2	5	0.2	15
Overall Result	5	1	10	1	15	1.5	20	1	25	1	75

*The regions are dynamic.

The % column is dividing the quantity of the region per material (1) to the overall result quantity which is 75.

-> 1/75 = 0.01 (Wrong)

Now, we want the % column to be quantity of the region per material (1) divided by the region's quantity overall result (5).

Example:

Region 1

-> Material Number = 2

-> Quantity = 1

-> Overall Result = 5

  -> % column = 1/5 = 0.2 (Correct)

It's a bit weird that SUMCT is not working on our end. SUMGT on the other hand is getting 70. What are we doing wrong and what are the missing steps to achieve this kind of output. Also, what are the settings for aggregation if ever needed.

Thanks, SCN!

[Former Member]

Hi,

Use data function (Overall Result) SUMGT against Quantity like SUMGT 'Quantity'. This gives value of 5 for each record. Use this in the formula of % column.

Hope this solves your problem.

Regards,

Dinkar Adem