14 Replies Latest reply on Oct 12, 2016 10:17 AM by Nicholas Nemirsky

# Average Data Across Dimensional Filter

Hello,

I have data set up like the below format that I cannot seem to get Tableau to average all together and show as one circle on a scatter plot.  Instead, it shows up as three distinct circles on the scatter plot.

 Name Value Calculated Dimension Filter A 70 Below 80 A 98 95+ A 90 80-95

When I add the dimension filter to the worksheet as color, it separates each row of Name A as the value that was shown in each of the three rows.  What I want it to do is take the average across (70+98+90)/3 and then show that as the final filter.  In this case, the average would be 86 and I would only want it to show once in the 80-95 range.

• ###### 1. Re: Average Data Across Dimensional Filter

If you're using Tableau 9+ this should be relatively straightforward using a LOD calc.

First get the average for each name: {FIXED [Name]: AVG([Value])}

That will give you the desired 86.

You can then build you buckets from that - i.e. IF {FIXED [Name]: AVG([Value])} >= 80 AND {FIXED [Name]: AVG([Value])} < 96 THEN '80-95' END

1 of 1 people found this helpful
• ###### 2. Re: Average Data Across Dimensional Filter

Can you post your workbook so far so we can see what you've attempted? We can probably tweak it to help you out more easily.

• ###### 3. Re: Average Data Across Dimensional Filter

Unfortunately, I can't post the workbook as it is confidential data.  However, I did get the LOD filter to work for data that was set as TODAY().  I did forget to mention that there are also dates in my data set.  So I would need to fix the dates as well?  What I've done is { Fixed [NAME],[DATE]: AVG([NUMBER])}.  The logic error I am getting is when I attempt to show the data for all time (>10 years) it creates a circle for each year in the scatter plot.  Any tips that anyone can give?

UPDATE - I got the IF {FIXED [Name],[DATE]: AVG([Value])} >= 80 AND {FIXED [Name],[DATE]: AVG([Value])} < 96 THEN '80-95' END to work by adding date in the fixed for each group.  But when I move the date filters to anything beyond one day, I still have the same issue where I get multiple circles for each category (80 and lower, 80-85, and 95+)

• ###### 4. Re: Average Data Across Dimensional Filter

As you're fixing it for each day it's returning a result for each day - hence you're seeing multiple marks.

You might need to change your formula. Should the change in date affect the average? Or should the average remain fixed (static) regardless of the dates selected?

• ###### 5. Re: Average Data Across Dimensional Filter

Hi Andrew,

The average should remain fixed (static) regardless of the dates selected.  For example.  I may want to view the average for last 30 days, QTD, YTD, and all time.  I would want to average the values any date range selected.  How could I modify the calculations for that?

• ###### 6. Re: Average Data Across Dimensional Filter

You can try removing Date from your LOD, then adding the date filter to context. I'm not entirely clear on what you intend to accomplish but I'm thinking this might be it.

• ###### 7. Re: Average Data Across Dimensional Filter

This did not work.  Any other suggestions would be helpful.  It seems like Andrew was on to something.  Hopefully he can respond again.

• ###### 8. Re: Average Data Across Dimensional Filter

Reading these posts to try and work out what is required here. It sounds like you actually want the average to change based upon dates filtered. In which case Ivan's suggestion of removing the date field from the calculation and adding the date filters to context should work.

What didn't work when this was tried?

• ###### 9. Re: Average Data Across Dimensional Filter

Nicholas, Andrew and Ivan are correct.

I built a twbx with your data and it worked.

• ###### 10. Re: Average Data Across Dimensional Filter

Hello,

Thanks for that.  It does work, but only for all times ranges with the date filter. I was able to attach a sample.  Modify the date filter and see that the color does not change when the numbers go into the other value ranges.  Is there an easy fix to this?

• ###### 11. Re: Average Data Across Dimensional Filter

I don't have Tableau installed on this machine so can't open the file. Hopefully someone else will take a look at it for you, or alternatively please provide some sample data in Excel/text file and screenshots of your workbook so I can recreate using Tableau Public.

• ###### 12. Re: Average Data Across Dimensional Filter

Hi Andrew,

Thank you for your willingness to help. Below is the data and attached are the pictures of what I need help with.

When I have filtered date ranges 10/1/16 to 10/6/16, the average is 103%, but the LOD calculation is showing that it falls in the 80-95 range.  Similarly, when I choose date ranges 10/7/16-10/10/16, the average is 56%, but also still shows in the 80-85 range.

This is the LOD calculation I used.

If     {fixed [Name]: Avg([Value])} <  .80 then 'Below 80'

ElseIf {fixed [Name]: Avg([Value])} <= .95 then '80-95'

Else                                           '95+'

End

I want Tableau to change the category of the percentages of data based on the range of dates I choose.  It is currently static at 80-95 since that is what the total average is of the entire data set.  Somehow Tableau is ignoring the date filter color.

 NAME VALUE DATE A 1.05 10/1/2016 0:00 A 0.98 10/2/2016 0:00 A 1.04 10/3/2016 0:00 A 1.04 10/4/2016 0:00 A 1.04 10/5/2016 0:00 A 1.04 10/6/2016 0:00 A 0.50 10/7/2016 0:00 A 0.50 10/8/2016 0:00 A 0.50 10/9/2016 0:00 A 0.75 10/10/2016 0:00 A 0.80 10/11/2016 0:00 A 0.85 10/12/2016 0:00
1 of 1 people found this helpful
• ###### 13. Re: Average Data Across Dimensional Filter

Try putting your date filter to context. Once in context it will be coloured grey, not the green it currently is. To put into context right click the little triangle to the right of the pill on the filters shelf and select Add to Context. The grey pills below are in context and I'm putting the Year pill into context.

• ###### 14. Re: Average Data Across Dimensional Filter

Hi Andrew!

This worked!  Thank you so much!  What does it mean to add to context?  I am not sure what exactly is happening behind the scenes, but am happy that it worked and is showing the data correctly!