2 Replies Latest reply: Mar 27, 2014 6:26 PM by Murti Akkime RSS

    How to use a calculated dimension within an expression

      Hi All! This is my first question on Communicty and hopefully it will help.  I have data which I want to show the results in Quartiles.  I have been able to successfully show this using a calculated dimension named 'Quartile' using the following code:

       

      =If(num(dollar_sales_total,'#,##0.##','.',',') <= $(v25PercentFractile),'Quartile 1',

           If(num(dollar_sales_total,'#,##0.##','.',',') > $(v25PercentFractile) and

           num(dollar_sales_total,'#,##0.##','.',',') <= $(v50PercentFractile),'Quartile 2',

                If(num(dollar_sales_total,'#,##0.##','.',',') > $(v50PercentFractile) and

                num(dollar_sales_total,'#,##0.##','.',',') <= $(v75PercentFractile),'Quartile 3','Quartile 4')))

       

      Now within a pivot table I want to have an expression which calculates the median sales value for the various quartiles.  Any suggestions on how I may be able to do this?  Thanks for the assistance.