Finding median while ignoring 0 values

[yxchua]

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]))

[bethany_ · Accepted solution]

Do you use the 0s elsewhere? You could change them in your load into a Null() rather than a 0 value so they are ignored by the formula.

[yxchua]

Thanks! It worked.