Median Calculation/Exclude NAN from MAX function

Hi team,

 

I'm attempting to calculate the Median revenue per customer over a period of 4 months (excluding negative revenue months due to credit notes etc) but cannot obtain the Max value per customer due to it returning the NaN value (if there is 1)

 

My process has been:

1) Module dimensionalised by Customer & a dummy Month list. Then 3 line items, Revenue per month, Greater than zero boolean & then RANK by Customer excluding the values less than zero. 

2) Create a Module dimensionalised by Customer List & with a line item returning the MAX value from the previous module. This always returns the NaN value rather than the greatest value

 

Is there a way to exclude the NaN value from the Max calculation?

 

clipboard_image_0.png

clipboard_image_1.png

 

Thanks

 

Mark

Best Answer

Answers