Semi Additive Measure in Drill-down hierarchy

[Marqus]

Hi, 

Let's assume that there is a retail business that have Customers Segmentations, Locations, Product Hierarchy. 

Number of Customers is not additive.  Displaying proper value in this context is simple with FirstSortedValue([n_customers], WEIGHT) 

When Period is added things get complex. 

I tryied

A/ FirstSortedValue(Aggr(Avg(NumberOfDistinctCustomers), CustomerType, Location, ProductsHierarchyLevel1, ProductsHierarchyLevel2, WEIGHT), WEIGHT)

B/ Avg(aggr(FirstSortedValue(NumberOfDistinctCustomers,WEIGHT), Period))

"A" works only if a single period is selected so it is an equivalent of simple FirstSortedValue(NumberOfDistinctCustomers, WEIGHT). 

"B" is partially successful, however it display only one value from dimension in a table

My question is: What should be the formula to display average values for all entries in the table and keep drilling down capability of hierarchy from first column. 

Please see attached example. 

Thanks in advance, any hint appreciated. 🙂

[Marqus · Accepted solution]

Solved it by myself and post for future reference. 

Second Argument of Aggr() must be 2 dimensions on which aggregaton happens. However one of them is dynamic and must be calculated with strange formula  [$(=Replace(GetObjectField(0),']',']]'))]

Full expression is as follows:

Avg(aggr(FirstSortedValue(NumberOfDistinctCustomers,WEIGHT), Period,[$(=Replace(GetObjectField(0),']',']]'))]))

[Frank25]

Hi Marqus,
your formula works very fine. 

I have found it after some days to looking for a solution for semi additive measure into a pivot table.

My solution uses if and GetObjectDimension() and is more complicated that your.

Can you explain me how this component works ?
[$(=Replace(GetObjectField(0),']',']]'))])) 
I have tried but I don't understand it.

Thanks