Question
Derrek Kegler · Mar 16, 2017

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.

0
0 225
Discussion (2)0
Log in or sign up to continue

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.