5 Replies Latest reply on Aug 18, 2017 7:56 AM by Jim Dehner

# Find Averages by Excluding Filtered Selection

I have a dataset with different charges (Base Charges, Total Charges, etc.) and I want to be able to calculate the average of these charges, but only when a condition for another field is not met/by excluding the condition selected in a filter. For example. I have a filter created from a parameter that defines the number of hours assigned to a job. If I choose "8 hours" from the filter, I want to be able to workout the average of Base Pay for jobs where the hours was NOT 8 hours.

I've attached a sample file here.

• ###### 1. Re: Find Averages by Excluding Filtered Selection

Hi

I'm not sure I understand your Viz terminology but have you tried

{ FIXED [Cp Id]:IF  sum([Hours]) > [Shift Hours]

then ([Average Base Pay]) else 0 end }

It should produce something like this

Let me know if this helps

Jim

• ###### 2. Re: Find Averages by Excluding Filtered Selection

I had another look at it and I think what might work better is something similar to:

IF [Hours] <> [Shift Hours] THEN [Average Base Pay] ELSE END

The reason for this is that I don't just want to show results greater than the Hours selected; I'd like any results (below or above), but just not the Hours actually chosen, if that makes sense.

Also curious to know why you chose to go with a FIXED expression in this instance?

• ###### 3. Re: Find Averages by Excluding Filtered Selection

Thanks for the follow up - and yes I did mis-interpret your question to think that it was limited to hours greater than shift only

to your second issue - It was not clear to me which of your dimensions would effect the calculation (I don't know your industry or the data base)

I decided to fix the value of a calculation creates permutations that will hold the aggregated value at the combination of any dimension(s) that precede the colon (:)

I think that I tried a couple of levels then decided to freeze it at just the CP ID level to make sure the calculation got the CP ID with multiple Clients - that may not make sense in the real world situation but that is the rational

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 4. Re: Find Averages by Excluding Filtered Selection

Ah I see, yes that makes sense! Thanks so much