5 Replies Latest reply on May 4, 2018 4:48 AM by David Maning

# Creating calculated field with subgroups of one dimension

Hi,

I have following workbook structure:

Where the arrows are, I want to add a field that provides the difference between 'BASE' and 'COMPARISON' figures.  My issue is that the BASE and COMPARISON figures are based on the one dimension; AVG(Travel Time), which is split up by 'Base or Select' depending upon a TRUE / FALSE boolean.

Does anyone know how to do this without having to duplicate the 'Travel Time (mins)' measure?  I know I how to achieve the right outcome if I duplicate the measure, but I wanted to know if there was a better way (or different way) to do this.

Regards,

Daniel

I apologise for the lack of a workbook, the underlying data is sensitive.  If the screenshot is not enough to work with, I can generate a sample dataset if required.

• ###### 1. Re: Creating calculated field with subgroups of one dimension

Hi Daniel,

• Create three calculated fields:
• [Base] = IF [Base or select] = 'Base' THEN [Travel Time] END
• [Comparison] = IF [Base or select] = 'Select' THEN [Travel Time] END
• [Difference] = IF [Base or select] = 'Base' THEN [Travel Time] END - IF [Base or select] = 'Select' THEN [Travel Time] END
• Remove [Base or Select] from the Columns shelf
• Remove Avg(Travel Time]) from text in the Marks shelf
• Drag Measure Names to the columns shelf
• Drag Measure Values to the Text tile in the Marks shelf
• Remove all measures from the measures shelf except for the three calculated fields listed above, and re-order as desired.
• ###### 2. Re: Creating calculated field with subgroups of one dimension

Hi Eric,

I tried your solution and it had other issues to address - such as the inclusion of 'base' and 'select' column for each date, even though the date is either base or select, not both.  So a blank column was created for each date.

I've attached a sample workbook that may assist.

Regards,

Daniel

• ###### 3. Re: Creating calculated field with subgroups of one dimension

Hi Daniel,

pls mark my response as correct and helpful following this topic So Your Question Didn't Get Answered...

Trust this helps.

D

1 of 1 people found this helpful
• ###### 4. Re: Creating calculated field with subgroups of one dimension

Hi David,

Your workbook was very useful and with a few minor changes, fit my scenario perfectly.

Thanks again!

Regards,

Daniel

• ###### 5. Re: Creating calculated field with subgroups of one dimension

You are welcome