4 Replies Latest reply on Aug 16, 2013 7:36 AM by alison.davis.0

# Filter by sum of a measure within distinct dimension

Hello,

My data set is much larger and more complex than this, but this sample should suffice. I have a set of data in Excel that is basically arranged like this:

 Unit Number Plant Name Capacity (MW) 1 AA 5 2 AA 6 3 AA 7 4 AB 4 5 AB 1 6 AB 9 7 AC 14 8 AC 33 9 AD 6 10 AD 27

I would like to create a filter that allows me to filter at the PLANT level (not the unit level) by sum of capacity. For example, I would like to be able to filter out all distinct plants that have less than 30 MW of capacity. Ideally, I would like to be able to filter along the scale of plant capacity.

Basically, I need to calculate the sum of capacity at each distinct plant, and then filter based on that sum. I do NOT want to filter based on the capacity at the unit level.

I am also filtering on multiple other dimensions and would like this filter to be used on top of those filters.

• ###### 1. Re: Filter by sum of a measure within distinct dimension

Alison,

You need a computed set for plants (Plant Name):

1) Right click on Plant Name, select Create Set

2) Click on Use All

3) Click on Condition tab

4) Click on By field and select Capacity (Sum), choose ">=" and type in 33

This set can be used a filter. See below and attached.

NOTE: If you want to use the In/out functionality (middle sheet below) and your data is in excel, you'll have to create an extract first.

Pedro

1 of 1 people found this helpful
• ###### 2. Re: Filter by sum of a measure within distinct dimension

Thank you Pedro, this is helpful! It would still be nice to have a sliding scale Quick Filter that would allow me to alter the range of the filter without going into the Edit Set screen, but this will serve my needs well.

• ###### 3. Re: Re: Filter by sum of a measure within distinct dimension

Alison,

You can change the set to use a formula instead of a field. The formula can compare the SUM([Capacity (MW)]) against a parameter [Select Min. Capacity]:

SUM([Capacity (MW)])>=[Select Min. Capacity]

Then you can use the parameter in the view to control the capacity threshold.

See the attached workbook.

Pedro

• ###### 4. Re: Re: Filter by sum of a measure within distinct dimension

Perfect, thank you! Much appreciated.