2 Replies Latest reply on Jun 17, 2016 11:09 AM by David Stachon

# Hover Over State to see State Trend Versus Overall

Hi,

I've attached a boiled down example of a problem I'm trying to solve...

I'd like to be able to hover over a state.

..then, below, the trend graph will appear and show the trend for that state. (that's easy, and working as expected)

My problem is, I'd like to plot the average of all the states (rolled up) for comparison, but, I'm stuck because the "Action" is filtering the trend on just that particular state I'm hovering on.

Very simple Workbook attached, or you can grab/play from Tableau Public:

https://public.tableau.com/views/StateTrendExample/StateAverageProfits?:embed=y&:display_count=yes&:showTabs=y

Any help, would be greatly appreciated.

I've seen solutions where the State is in a parameter list, but this will not be practical with my actual application of this technique (if I can get it to work).

Thanks!

David.

• ###### 1. Re: Hover Over State to see State Trend Versus Overall

Hi David!

We should be able to accomplish this using an LOD calc.  I attached a revised copy of your workbook, hopefully I didn't misunderstand the desired end result

To start with, I removed the Action dimension you had on Columns for the Trend worksheet.  Next, I created a custom date, [Order Date (Month / Year)] because we will need to use that date value in the LOD calc.  Next, I used this formula for the LOD:

{ fixed [Order Date (Month / Year)] :

AVG({ fixed [Order Date (Month / Year)], [State] :

AVG([Profit]) }) }

For the Trend worksheet, I placed the above LOD calc onto Rows and made the view a Dual Axis and also synchronized the axes.  I then made some color formatting changes to make the individual state avg and the overall US avg contrast each other a bit for ease of viewing.  The end result gives us something like this when hovering over a state:

Hope this helps!  Let me know if you were hoping for something different though

Thanks David!

1 of 1 people found this helpful
• ###### 2. Re: Hover Over State to see State Trend Versus Overall

Oh, that's really cool! thank you.

LOD calculations...

...definitely need to spend more time with them, and this is a great start.