Aggr by two dimensions

[Olip]

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

[sunny_talwar]

May be this

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

[YoussefBelloum]

Hi,

find attached.

Hope it is what you're looking for

[Olip]

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)

[YoussefBelloum]

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 ?

[sunny_talwar]

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.

[Olip]

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.

[Olip · Accepted solution]

Thanks Sunny.

The total does not help owing two Dimensions.

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

[YoussefBelloum]

this expression is wrong.

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

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