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

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:

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.

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!

• ###### 1. Re: Calculated field expression will change based on dimensions used in column pill holder

Vincent,

I'm wondering if you may be able to use the SIZE() function to determine which condition you are in.

In the attached example, the Size starts off as 2, but when Product is added, it goes to 26.

If your initial condition has a unique size number, then your conditional can just be:

IF [SizeField]=2 THEN avg([Budget Margin])*100

ELSE SUM([Budget Margin])

END