Subtotal and Average by Parent

[jim_juliano]

With BPC 10 SP13 for MS, I am attempting to add both a subtotal calculation and average calculation at each change in the H1 parent property.

I can add a subtotal with Member Sorting & Grouping, but need to also add an average for the same group.

It doesn't appear I can attach an additional local member to the "After" local member created with Sorting & Grouping.

Is it possible with to group by the member property with EPMINsertBeforeAfter, or with a local member such as EPMSelectedMembers or EPMAllMembers?

The data should appear as follows, in this scenario A.1-A.4 would be part of the H1 parent A:

	Units	Aug-14
A.1	180	$ 947
A.2	72	$ 1,130
A.3	192	$ 1,286
A.4	36	$ 1,614
Average A		$ 1,244
Total A		$4,977

Thank you!

[Former Member]

Hi Jim,

we can do it by following two steps:

  1. Change the default formula syntax 'SUM to 'AVG' in Member Sorting& Grouping ;

like: =SUBTOTAL(9,EPM_SAME_PROPERTY(TIME,47932f46-b7b1-4207-b693-d9f7a18aaaed))

changer to: =SUBTOTAL(1,EPM_SAME_PROPERTY(TIME,47932f46-b7b1-4207-b693-d9f7a18aaaed))

  2.Create a localmember insert after row axis with formula: =SUM(EPMALLMEMBERS

pls try this way, hope it can resolve your query.

Message was edited by: Harry Liu