Alter your formula to this and see if you still have NULLs:

IF ([Total Amount]) <= 25 THEN '$1-$25'

ELSEIF    ([Total Amount] >=26 AND [Total Amount] <=50) THEN '$26-$50'

ELSEIF  ([Total Amount] >=51 and [Total Amount] <=75) THEN '$51-$75'

ELSEIF  ([Total Amount] >=76 and [Total Amount] <=100) THEN '$76-$100'

ELSEIF  ([Total Amount] >=101 and [Total Amount] <=125) THEN '$101-$125'

ELSE 'Greater than 125'

END

If it works as expected you'll get a lot of 'Greater then 125'. If you think this shouldn't be returned please provide some data or a twbx file showing where it isn't returning the expected results.

I suspect I know what's going on, try this:

IF (SUM([Total Amount]) <= 25 THEN '$1-$25'

ELSEIF    SUM([Total Amount]) >=26 AND SUM([Total Amount]) <=50 THEN '$26-$50'

ELSEIF  SUM([Total Amount]) >=51 and SUM([Total Amount]) <=75 THEN '$51-$75'

ELSEIF  SUM([Total Amount]) >=76 and SUM([Total Amount]) <=100 THEN '$76-$100'

ELSEIF  SUM([Total Amount]) >=101 and SUM([Total Amount]) <=125 THEN '$101-$125'

ELSE 'Greater than 125'

END

If it doesn't work please provide some data or a twbx - a screenshot of the result isn't that useful to help troubleshoot

Hi Shesil,

Try this.

Thanks

Deepak

IF ([Total Amount]) <= 25 THEN '$1-$25'

ELSEIF    ([Total Amount]) <=50 THEN '$26-$50'

ELSEIF  ([Total Amount]) <=75 THEN '$51-$75'

ELSEIF  ([Total Amount]) <=100 THEN '$76-$100'

ELSEIF  ([Total Amount]) >=101 THEN '$101-$125'

END

Formula is good it appears. It worked for me. I tried it on the sample - EU superstore dataset. I replaced your [Total Amount] with [Sales]

IF ([Sales]) <= 25 THEN '$1-$25'

ELSEIF    ([Sales]>=26 AND [Sales]<=50) THEN '$26-$50'

ELSEIF  ([Sales]>=51 and [Sales]<=75) THEN '$51-$75'

ELSEIF  ([Sales]>=76 and [Sales]<=100) THEN '$76-$100'

ELSEIF  ([Sales]>=101 and [Sales]<=125) THEN '$101-$125'

END

This is sales number format I am using. I hate to state the obvious and I'm not insulting your intelligence, but are you sure your data set has values of between 50 and 125? I mean if you are getting nulls that may be the case. Try changing the actual numbers and see what you get. I'd be curious.

Try this.

IF ([Total Amount]) <= 25 THEN '$1-$25'

ELSEIF    ([Total Amount] >=26 AND [Total Amount] <=50) THEN '$26-$50'

ELSEIF  ([Total Amount] >=51 and [Total Amount] <=750) THEN '$51-$750'

ELSEIF  ([Total Amount] >=751 and [Total Amount] <=7000) THEN '$750-$7000'

ELSEIF  ([Total Amount] >=7001 THEN '$7001'

END

Shesil, yes for now we know the formula is correct....at least for a diff dataset.

All I am saying is that formula would be showing the correct behavior of only showing the first two IF the dataset did not have those values. Look at the disproportionality here too. Pull an avg for total amount out of those 30 million records. Is it around 20-40?

But since you are assuring me that there are values in the dataset between 50 and 125 I won't belabor the point anymore

Just to experiement (I assume you are open to other ideas), can we try the formula on a different measure  besides [total amount] in that dataset?

I'm curious also is [total amount] a native or calculated field. If calculated what is it.

JPB