8 Replies Latest reply on Aug 29, 2016 7:14 AM by Jaspreet Narang

# Trend Lines in Dashboard

Hi Everyone,

I am trying to show a trend line in my dashboard but it seems like the average value being displayed is not correct (Version 9.0).

The average value for 8/15 is 7.4 and for 8/22 is 12.1 in my data source but the values displayed on the dashboard is different

The data source that I am using to create this has 3 tabs. The first tab is joined to the second using Project ID as the Primary key and joined to the third using Sponsor as Primary key.

I have attached the workbook and the data sources also.

Thanks,

Jesse

• ###### 1. Re: Trend Lines in Dashboard

The problem is the join returns duplicates for the "team members" field you're taking an average of. Go to analysis -> view data -> underlying. Then look at the field you are taking an average of and you will see all the duplicates.

I've thought about this problem briefly before and couldn't come up with a simple solution within the workbook, but i would love to hear one if anyone has one.

You could use custom SQL to create a field that populates nulls for all but one for each team, or just using a separate data source without duplicates for that specific sheet/chart may be easiest.

1 of 1 people found this helpful
• ###### 2. Re: Trend Lines in Dashboard

Hi Chris,

Thank you for the response. I will try doing it with a separate data source and let you know of the results.

Best Regards,

Jesse

• ###### 3. Re: Trend Lines in Dashboard

Appears that your data shows sum of Team Members for Aug 15 is 615, count is 87; so the average = 7.1. The calculation is right based on underlying data.

1 of 1 people found this helpful
• ###### 4. Re: Trend Lines in Dashboard

Hi Naveen,

Thank you for the response. I think the problem here is that I have 3 tabs and the join with the second one is causing the problem. For example a project has 4 team members and has 3 high issues so the total comes out to be 4 x 3 = 12 members instead.

Because for Aug 15 my count is only 140 and average is 7.4

I can't seem to figure out a solution for this.

• ###### 5. Re: Trend Lines in Dashboard

Hi Chris,

I tried doing it with a different data source with no duplicates but the dates are completely messed up. It says 12/30/1899 12:00 AM instead.

• ###### 6. Re: Trend Lines in Dashboard

This will be difficult for others to help with, as its an issue with the underlying data not really tableau.

With the original workbook create a field using this:

SUM({ INCLUDE [Project ID (Team Members)] : sum([Team Members (Team Members)]) / count([Project ID (Team Members)]) })/ COUNTD([Project ID (Team Members)])

Then drag that to the row shelf.

See the attached as an example.

That would work except for you have multiple values for one of your teams...

 Date Project ID (Team Members) Team Members (Team Members) 8/15/2016 Workplace1 10 8/15/2016 Workplace1 5 8/22/2016 Workplace1 5 8/22/2016 Workplace1 15 8/22/2016 Workplace1 20
1 of 1 people found this helpful
• ###### 7. Re: Trend Lines in Dashboard

Hi Chris,

I didn't even realize the project id was same for some projects. Thanks for pointing it out. I will try the calculated field on my view and let you know of the results.

Best Regards,

Jesse

• ###### 8. Re: Trend Lines in Dashboard

Hi Chris,

Thank you so much for your help. That worked like a charm.

Best Regards,

Jesse