8 Replies Latest reply: Feb 6, 2018 11:35 AM by Youssef Belloum

# Aggr by two dimensions

Hello Experts,

Need help in sorting out this solution.

Requirement is to create a Percentage KPI by both the dimensions grouped by Dim 1 & 2.

KPI is for every combination of Dim B we need to find percentage against Dim 1

i.e for Dim 1 row 1 = 10/(10+30+50)

Furthermore there is a need to calculate percentile as well.

Sample data below:

 Dim 1 Dim 2 Measure One A 10 One B 30 One C 50 Two D 90 Two E 1000 Two F 200 Three G 700 Three H 890 Three I 12 Three J 123 Three K 450 Three L 800
• ###### Re: Aggr by two dimensions

May be this

Sum(Measure)/Sum(TOTAL <[Dim 1]>Measure)

• ###### Re: Aggr by two dimensions

Hi,

find attached.

Hope it is what you're looking for

• ###### Re: Aggr by two dimensions

Thanks Youssef!

Looks like both the formulae works. I tried the former.

Sum(PEB)/aggr(nodistinct sum(total <Segment>(PEB)),Segment)

&

Sum(PEB)/aggr(nodistinct sum( <Segment>(PEB)),Segment)

• ###### Re: Aggr by two dimensions

Good,

but you should take one of these two:

Sum(PEB)/ Sum(total <Segment> PEB)

Or

Sum(PEB)/aggr(nodistinct sum(PEB),Segment)

in terms of performance, stalwar1 will maybe give us his feedback ?

• ###### Re: Aggr by two dimensions

I try to avoid Aggr() function as much as I can, but more recently, I have seen issues where TOTAL doesn't really work well and had to fall back to Aggr() with NODISTINCT. So, I would first try to see if TOTAL qualifier works, if not, then fall back to Aggr() with NODISTINCT.

• ###### Re: Aggr by two dimensions

Thanks Sunny.

The total does not help owing two Dimensions.

So i went with ' sum( (Measure)/aggr(nodistinct sum( Measure),Dim1)'

• ###### Re: Aggr by two dimensions

this expression is wrong.

if you choose the Aggr alternative, you should use this:

Sum(Measure)/aggr(nodistinct sum(Measure),Dim1)

• ###### Re: Aggr by two dimensions

Sum(Measure)/ Sum(total <Dim 1> Measure) does not work as 'Sum(total <Dim1> Measure)' returns the same as a normal sum. Hence, aggr is the only option removing the total is an option though as the latter.