2 Replies Latest reply: Oct 20, 2017 10:29 AM by Abhijeet Mali

# Calculated Dimension over a measure to use in a filter Item?

Hello Everyone,

I need a help with creating a calculated dimension over 4 different measure to be used in a filter pane. And also mix and match these filters to investigate the data further.

I have data similar to the following table -

 Customer Name Year Total Impact Category 1 Impact Category 2 Impact Category 3 Impact A 2017 \$82.1K \$30.1K \$27K \$25K A 2016 \$519.5K \$190.3K \$171K \$158K A 2015 \$641.1K \$234.8K \$211K \$195K B 2017 \$686.6K \$251.5K \$226K \$209K B 2016 \$461.9K \$169.2K \$152K \$140K B 2015 \$2,154.2K \$789.1K \$710K \$655K

The total impact column is the summation of  impacts across all three categories

Sliding scale to create 'Impact' dimensions on all three measures is as follows -

If the value is less than zero then Negative

If the Value is equal to zero then Zero

If the Value is greater than zero then Positive

Initially I tried to create calculated dimension for category 1 impact as follows (to be used it in filter pane) -

if([Category 1 Impact]<0 'Negative', if(Category 1 Impact) = 0, ' Zero', 'Positive')

And followed the same logic for developing other 3 dimensions

Which works fine when I wanted to see if any of the customers for all the years have at least once Negative, Positive or Zero Impact. Also all the filters work in combination with each other. However I did not wanted this functionality my intention was to create dimensions so that they work on aggregation of these measures across all the years by customers

Hence, I created calculated dimension as follows (to use in the filter pane) -

IF(Aggr(Sum([Category 1 Impact]), [Customer Name])<0 'Negative' IF(Aggr(Sum([Category 1 Impact]), [Customer Name]))= 0, 'Zero', 'Positive'))

These calculated dimensions in a filter work fine when used individually, but do not work in conjunction (Initially applied filter is not being retained when I try to select a value from another Impact filter) with each other. My problem is I want to be able to mix and match these filters to derive further insights from the data.

For example - Let's say I want to filter customers who had negative impact in category 2 but positive impact on category 1 and negative in category 3. but I am not able to do it with aggr filters

I am also attaching a sample QVF and Excel file.

Thanks!

• ###### Re: Calculated Dimension over a measure to use in a filter Item?

See if the attached is what you want

• ###### Re: Calculated Dimension over a measure to use in a filter Item?

Hi Sunny,

Thank you for the reply. This is not what I wanted. I want the filters on Sheet 1 to act like filters on sheet 2.

Meaning When you select any filter value from impact filters on the first sheet you see Customer Name filter in the filter pane instead of the Impact filter unlike Sheet 2.