10 Replies Latest reply on Jul 25, 2016 4:33 PM by Ashish Chaudhari

# Show me only values greater than Average

I know how to do calculate sub totals using average. After calculating the average, I just want to see the the values more than average.

I have used sample super store to create an example. So my question is for furniture category, How can i have values only greater than 185,500 (average) ?

• ###### 1. Re: Show me only values greater than Average

Hey Harshil,

You can use a Level of Detail calculation to determine the SUM of each Sub-Category, then get the average of these with another LoD calculation at the Category level.

Then, you can compare this to the current sub-category's SUM.

SUM([Sales]) >

MAX({

FIXED [Category] :

AVG({FIXED [Category], [Sub-Category] : SUM([Sales])})

})

Then, simply use this as a filter on your view.

• ###### 2. Re: Show me only values greater than Average

Like this:

• ###### 3. Re: Show me only values greater than Average

If I add Manufacturer then the average is not working . So even if i add more fields the average should be calculated accordingly ?

Thanks

• ###### 4. Re: Show me only values greater than Average

Hey Harshil,

With the LoD method, you will need to add Manufacture to your base calculation.

With the Window AVG method, you will need to change the "compute using".

1 of 1 people found this helpful
• ###### 5. Re: Show me only values greater than Average

So I tried adding Manufacture and it is showing me average accordingly ... so that is awesome

Just a last question. I dont know what is windows_ave ? Is it just average by pane ?

• ###### 6. Re: Show me only values greater than Average

Hi Harshil,

Put this in the filter shelf and select "True". That's it.

Name - Filter

SUM([Sales])>(SUM({ FIXED [Category]: SUM([Sales]) }) / SUM({ FIXED [Category] : COUNTD([Sub-Category]) }))

Please find the attached screenshot and confirm the same. Also find the attached workbook for your reference.

I hope this one helps.

Thanks and Regards,

Ashish Chaudhari

• ###### 7. Re: Show me only values greater than Average

Hello Harshi Bhatt..

1.

2.

3. Right click sales, add quick cal and select moving average.

Hope this will be helpful to you.

• ###### 8. Re: Show me only values greater than Average

Don't apply any quick cal for the sales. Sry for the wrong information.Below capture is the right answer.

• ###### 9. Re: Show me only values greater than Average

The value has to by dynamic .. not fixed(185500) ... it keeps changing

• ###### 10. Re: Show me only values greater than Average

Hi Harshil,

yes it's average by Pane. Is your problem really solved or still seeking for an answer?

if you are still looking for an answer try my calc and let me know if that works for you or not.

Thanks and Regards,

Ashish Chaudhari