Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to find the median of "Rental to sales" value by Name, ignoring the 0 values.
However, I noticed the median function takes 0 values into consideration. How could I find the median value without the 0 values? Thanks.
My calculation for Rental to Sales:
(-1*
sum(aggr(
Sum({$<[PLLevel6]={'RENTAL'},Valid={'Y'}>}Amount),
[ID],[MonthYear])))
/
(sum(aggr(
if([Remote/Retail]='Remote'or [Category] = 'OCB',
Sum({$<[PLLevel5] = {'Sales'}>}Amount),
if([RetBranCategory]='Branch',
Sum({$<[PLLevel5] = {'Sales'},Valid={'Y'}>}Amount)+
Sum({$<[PLLevel5] = {'Sales'},Name={'TOTE'}>}Amount),
Sum({$<[PLLevel5] = {'Sales'},Valid={'Y'}>}Amount)
)),[MonthYear],[ID])))
Finding Median:
median(aggr((-1*
sum(aggr(
Sum({$<[PLLevel6]={'RENTAL'},Valid={'Y'}>}Amount),
[ID],[MonthYear])))
/
(sum(aggr(
if([Remote/Retail]='Remote'or [Category] = 'OCB',
Sum({$<[PLLevel5] = {'Sales'}>}Amount),
if([RetBranCategory]='Branch',
Sum({$<[PLLevel5] = {'Sales'},Valid={'Y'}>}Amount)+
Sum({$<[PLLevel5] = {'Sales'},Name={'TOTE'}>}Amount),
Sum({$<[PLLevel5] = {'Sales'},Valid={'Y'}>}Amount))),[MonthYear],[ID]))),[Name]))
Thanks! It worked.