Hi Kiran Kumar,
I guess if i understand correctly. You want to find Average based on number of children instead of SUM.
In BPC always into hierachy you will have the parenth like sum of children.
In your case you can use dimension formula wher you don't need to addup all children numbers into parent but into property formula from your dimension you will specify that this member will be calculate average.
You have also MDX formula where you can specify that it is Average of all children.
Anothher solution will be to add a custom measure
(You have an HTG published into BPX/SDN regarding custom measure) to be calculate always like average of children.
I hope this will help you.
Rajesh Muppala.
Dear kiran kumar thamatam,
I have an idea for you case. If you had simple hierarchy for your member, let say parent and child. Please you do some tricks from me as following:
1. You could provide some columns in Ms.Excel likes: Account (column A), Parent (column B)
2. Please you use Ms.Excel function SUMIF for calculate the number of members and put it in somewhere cell. The function SUMIF could have some filter criteria for count the number of members, You could reference column B for SUMIF filter criteria.
I apologize for could not provide more information SUMIF format because I really didn't know your dimension hierarchy. Please you refer the Ms.Excel helps for more information how to use SUMIF.
Kind Regards,
Wandi Sutandi
What wandi said would work, but I would use COUNTIFS
- add a parent column (assuming you already have a property that mimics PARENTH1)
- your expansion will have to expand on self,all
- Your sheet will look like this
Col A - Entity ID
Col B - Parent property
Col C - CountIF
A B C
1 E1 2
2 E2 E1 =COUNTIF(A2,$B$1:$B$9)
3 E3 E1 0
4 E4 5
5 E5 E4 0
6 E6 E4 0
7 E7 E4 0
8 E8 E4 0
9 E9 E4 0
You can do this on a hidden worksheet and do a vlookup on the sheet you need the number in.
Add comment