HELP! Dynamic Aggr in pivot table

[tduarte]

Hi community,

Here's a challenge. Hope someone can help.

I need to flag a row when a Reseller represents more than 75% of Sales in any combination in the pivot.

There can only be a maximum of 1 so I'm doing a Sum.

So far, I only managed to get the flag down to the most granular view (table fully expanded). The partial total for Vendor is therefore incorrect as it should only be either 0 or 1.

The expression >75 is "static" at the moment and I'd like to make it dynamic (pivot aggregation) and if possible simplify it.

# Reseller represent the count of distinct resellers.

Pivot:

>75 expression:

Sum(

     If(

          Aggr(

               Sum(Sales) /

               Sum(TOTAL <ResellerSizeBand,Country,Category,Vendor,Month> Sales)    

          ,ResellerSizeBand,Country,Category,Vendor,Month,ResellerID)

     > 0.75, 1,0)

)

Thanks,

Telmo

[tduarte]

Experts, I'm kind of stuck at the moment.

Any ideas?

[tduarte]

I guess this a really tricky one.

I think it would help if there was way of referring to the upper level total or partial sum.

Is there?

[tduarte]

An expression for each dimension using Pick(Dimensionality()+1 sounds like an alternative as long as Pivoting is not allowed.

This thing is , I need to allow pivoting.

I doubt that it is possible but I need to aggregate dynamically based on the pivot dimension PLUS an extra one. This way I could allow pivoting.

For example:

for 1st dimension in pivot - Aggr(Sum(Sales),1stDimension,ResellerID)

for 2nd dimension in pivot - Aggr(Sum(Sales),1stDimension,2ndDimension,ResellerID)

etc...

Resuming, the default pivot aggregation plus ResellerID - a dimension not in the pivot.

Does anyone have any ideas or suggestions?

Thanks.