I need urgent help with a weighted formula. Currently I am using a ConsolidateChildren to calculate the Weighted Measure. I am aware that this has an impact on the RAM and performance and so does the client by now.
I cannot get any way around this.
My formulas:
## At an All Activity level it is a Total Case Cost and not a Case Cost.
['Case Cost']=
If (!Activity @= 'All Activities',
ConsolidateChildren('Activity'),
['Total Activity Cost']\['Cases']);
###I think that this rule is the issue.
###On a Item level the Case Cost and Weighted CAse Cost will allways be the same. But if Item is a grouping then Consolidate the children under the item grouping.
#Calculate Weighted Costs
#Cases
['Weighted Formula Calc']=
IF(ELLEV('Item',!Item)=0,
['Case Cost']*['Cases'],ConsolidateChildren('Item'));
##Again on a Item level the Case Cost and Weighted Case Cost will allways be the same, but the weighted formula calc measure is consolidated and not calculated at a item grouping level. Then take that Consolidated value and devide by number of cases and that give you a weighted case cost at that level.
['Weighted case cost']=
['Weighted Formula Calc']\['Cases'];
See view below:
This data is allready at a consolidated level. All Activities
I cannot get the table below in correct format. My fisrt post
838238 is the grouping that should be weighted.
181707.94 is the weighted formula calc that is devided by 60173 cases to get to the weighted case cost of 3.02
Items: Total Activity Cost Cases Case Cost Weighted Formula Calc Weighted Case Cost
838238 26 933.81 60 173.29 3.04 181 707.94 3.02
1162497 5 253.83 13 803.71 2.61 36 004.75 2.61
1162514 5 549.30 14 943.57 2.48 36 993.24 2.48
2869218 2 268.24 5 403.00 2.85 15 391.28 2.85
2869225 2 423.54 5 787.00 2.81 16 270.31 2.81
2869232 2 123.99 4 530.00 3.25 14 702.71 3.25
2869249 3 493.21 2 946.00 7.95 23 411.23 7.95
2869256 2 806.00 6 484.00 2.87 18 631.96 2.87
2869263 3 015.70 6 276.00 3.23 20 302.46 3.23
If I replace the above rule for 'Weighted Formula Calc' with rule below to not use consolidate children:
['Weighted Formula Calc']=N:
['All Activities','Case Cost']*['Cases'];
My performance is no issue and the RAM stay consitent but on a YTD or Q1 and Q2(not on a month) the weighted case cost is not weighted.
Any suggestions will be much appreciated.
Thank you