Workout Wednesday – Week 30

This week’s challenge is a throwback from Emma Whyte to a 2017 WOW challenge. It’s a scatterplot of sales and profit by state, but when you click on a state, the points shift to cities in that state. Emma used set actions, and you would think that would make things fairly easy, but they’re still plenty complex.

I started out with SUM(Profit) on Columns and SUM(Sales) on Rows. I knew I would need a set, so I created a State Set:

Then I spent twenty minutes trying to figure out how to use the set to switch from state to city in the view. After trying several different options, I thought I would check out Lindsey Poulter‘s Set Actions workbook, and it had just the direction I needed.

I started with a Dimension calc, returning the city if there’s a state in the State set, otherwise returning the state:

Next, I created a calc that returns a string of ‘State’ or ‘City’ depending on what the Dimension value is:

Then I created a calc to filter based on the number of distinct Level values:

If there is a state in the State Set, there will be two values in Level, ‘State’ and ‘City’. If there’s not, there will only be one value, ‘State’. So the Fixed LOD of {COUNTD(Level)} will return 1 if no state is selected, and then ‘Keep’ those records with ‘State’ in Level. It will return 2 if one (or more) states have been selected, and then ‘Keep’ those with ‘City’ in Level. When you add that to the Filters, and select Keep, it will filter to the states if no state is selected, or the cities if one is.

So I added Dimension to Detail and Label, and Level and State to Detail. Then I needed to set the color based on Profit:

If the SUM(Profit) fixed at the Dimension level is less than 0, make it red, otherwise blue. Then add that to the color shelf, set the red and blue accordingly, and I ended up setting it about 65% opacity.

Then I needed to touch up the tooltip: