Dollar Range
I am building a pivot table and need help creating a calculated field for a dollar range. For instance, I am trying to create a calculated field to show account balance greater than $5k and less than $10k.
Comments
Hi Derrek,
I suggest taking a look at the IIF Documentation. I think this is what you are looking for. Here is an example:
>> WITH MEMBER [Measures].[TestIIF] As 'IIF((([Measures].[Amount Sold]>500) AND ([Measures].[Amount Sold]<3000)),[Measures].[Amount Sold],"")' SELECT {[Measures].[Amount Sold],[Measures].[TestIIF]} on 0,NON EMPTY [Product].[P1].[Product Category].Members ON 1 FROM [HOLEFOODS]
Revenue TestIIF
1 Candy $162.49 *
2 Cereal $402.61 *
3 Dairy $105.95 *
4 Fruit $977.62 977.62
5 Pasta $1,408.68 1,408.68
6 Seafood $238.70 *
7 Snack $4,195.20 *
8 Vegetable $552.95 552.95
Hi, Derrek!
If the balance is a measure you can make the measure searchable and you would be able to use it in complex filters with >< operands.