Dynamic aggregation in key figure calculation

[former_member706491]

Hi All,

I have a requirement to do dynamic aggregation for the selected period.

To calculate weighted average, we use below formula.

∑(Consensus forecast accuracy*actuals sales Qty) / ∑ (actual sales Qty)

Ex. Month March 2019 has below values

Actuals Sales Qty Consensus Forecast Accuracy PROD A 8000 100 PROD B 1505 84 PROD C 9200 60

Weighted average is as below...

{(100*8000)+(84*1505)+(60*9200)} / sum(8000+1505+9200)

{800000+126420+552000+1478420} /18705

1478420 / 18705 = 79.03

Here, total number of key figure values are not constant. So, not able to do sum of key figure values dynamically.

Let me know if you knows how to define formula for such type of calculation.

Regards,

Kiran

[piyush_parekh · Accepted solution]

Hi Kiran,

Please check if below configuration works.

1. Configure a helper key figure HKF@MTHPRODLOCCUST = Accuracy@MTHPRODLOCCUST * ActualsQty@MTHPRODLOCCUST

HKF@Request = SUM (HKF@MTHPRODLOCCUST)

2. Change the request level of Accuracy key figure as below :

Accuracy@Request = HKF@Request / ActualsQty@Request

Make sure you have aggregation - SUM in the request level calculation of Actuals Qty. Also, add NULL/0 check condition for the denominator in the Accuracy key figure request level to avoid SQL error in Excel UI.

You can also refer Model Configuration Guide in which a similar configuration is explained.

https://help.sap.com/viewer/bf99e931b8d44aafb4e306ec3602cbdd/2002/en-US/b5263c55a5ef9a2de10000000a17...

Hope this helps!!

Regards,
Piyush

[former_member706491]

Please find the image for key figure values...