1 Reply Latest reply on Aug 3, 2016 5:34 PM by swaroop.gantela

    Calculated field expression will change based on dimensions used in column pill holder

    Vincent Sanchez

      Hi Everyone,

       

         Thank you again for your help in my endeavors. I'm finding myself with quite the curious requirement for a specific calculated field. My team lead has told me that the calculated field (Budget Margin Calculation) will have to change its underlying expression when specific dimensions are dragged and dropped into the columns pill holder (for lack of a better term?). Here is an example:

       

      one.png

       

        The calculated field remains with its initial expression of AVG(Budget Margin)*100 because in the columns holder, we only have the timeUnit dimension present. Now, the second case, we add lets say another dimension in which to slice data.

       

      two.png

        We have added the dimension Product Type to the columns pill holder. Now, the calculated field will need to change to have the expression SUM(Budget Margin).

       

       

        Its a curious requirement that I have been trying to wrap my mind around. I thought of maybe creating a calculated field that will review the contents of both the timeUnit and Product Type using several if and elseif statements but it didn't work as expected.

       

        I was also thinking about creating two separate worksheets (one to house the calculated field with only the timeUnit in the column pill holder and the other worksheet will have both the timeUnit and ProductType dimensions in the columns pill holder) but I wanted to see if there was a method in which I can keep it down to one worksheet. Any help is appreciated!