We are having an issue with KPI reporting.
The scenario is as follows:
I have KPI 1 = 30 (300/10) in Segment A
KPI 1 is 40 (800/20) in Segment B
Segment C is the parent of Segment A and B.
Now in the report, KPI 1 for Segment C is coming as 70.
But I need KPI 1 to be calculated as (300+800)/(10+20) = 36.67 for Segment C
Similarly, Segment C is a child of some other parent, so on and so forth, and at every parent level, I need the weighted average rather than a aggregation.
Any help on this would be appreciated!
P.S The model is a YTD model.