4 Replies Latest reply on Jun 11, 2018 1:49 AM by Shivi Bhatia

# Identifying Months Above Average Age

I am working on one of the use case where i need to find what all months had age higher than average age. The graph is in shape of a bar chart as attached below.

I need to create a calculated field which tells me which months have age higher than average age and use it as on the color tab.

Request assistance.

• ###### 1. Re: Identifying Months Above Average Age

Hi Shivi,

Since you didn't provide the data set I created my Own kindly follow the below Steps to achieve your requirement

1) Data set:

2) Bar Graph Plot:

3) To Calculate the Average Value and Identify what are the months more than Average

a) One with Exclude calculation (by removing the month Granularity)

b) Other Calculation using Fixed (Note : filter wont work until you add the filter to context)

4) Drag the Calculated field to Color section in Marks Shelf

Now you can get the Months above average in blue color and less than average in Grey-blue color. Hope this helps.

If this solution didn't resolve the issue kindly attach the twbx will try to help you out.

If this solution helps Kindly Marks this is question is completed so that it will help others.

Thanks,

Naveen Bandla

• ###### 2. Re: Identifying Months Above Average Age

Thanks Naveen.

I tried the first calculation but it gives only one category as "yes" . The calc i have tried is :

if SUM([Age])> AVG({EXCLUDE [Month]:AVG([Age])})

then "No"

else "Yes"

End

This only gives me one category as "Yes". Unfortunately as this is a client data i cant share it. I have copied the data that i am using without any other details.

Please let me know if this works else i would share another file creating only from this data.

Month Avg. Age

April 4.252982865

February 4.6625

March 5.108969963

January 5.884420736

December 65.769354839

November 98.259204713

October 124.885017422

September 264.642857143

May 292.384615385

August 298.363636364

June 298.737704918

July 319.269230769

• ###### 3. Re: Identifying Months Above Average Age

Hi Shivi,

I am not very sure on how exclude works so I am going with the Fixed approach similar to the answer mentiooned above.

From the data you pasted. Calculate the following fields

This when put in dashboard looks like this.

The calculation seems working and you can use the comparison field in color Marks

1 of 1 people found this helpful
• ###### 4. Re: Identifying Months Above Average Age

Thanks Jeevan, this worked as i needed.

Could not have thought that it would have been so simple.

Regards, Shivi