To close out our third week at The Data School, DSAU25 had the pleasure of doing our first Work Out Wednesday and presenting it.  We had the choice between a WOW that included a table calculation or a LOD function.  I chose the LOD function route as I felt that I could benefit from learning more about them.  After trying to find a challenge that looked challenging but also not impossible I settled on Week 31 from 2023.  As you can see from their provided screenshot we needed to find the most popular Sub-Categories in the most states.  The key to this viz is using a nested FIXED function to get the sales per State and Sub Category.  I also ventured into tooltip visualizations which I hadn’t tried before.  Below I will take you through the steps I took to recreate the visualization above and complete my first Work Out Wednesday.


Investigation and Calculations

At first I needed to figure out exactly what I was trying to visualize and thanks to the hint on the website I knew it would take a nested FIXED function.  I started by putting State, Sub Category and Sales into a table and sorting it.

This is the beginning of what I wanted, it shows my the most popular Sub-Categories in each state.  I then found the highest value for each State and Sub Category and did that with a nested FIXED calculation:


{FIXED [State/Province]: MAX({FIXED [State/Province], [Sub-Category] : SUM(Sales) })}


This calculation first fixes on the State and Sub Category to give me the sum of sales, which is what the first screenshot shows.  Then I nest that in another fixed statement, fixing on State to give me the Max value of each State/Sub-Category.  When put in the plot it shows only the Max value for each State & Sub Category, as seen below.  This is the value we want to use for the next part of the solution.



Now that we have a dynamic value that we want to compare to we will create an IF statement.  This IF statement will return the Sub-Category name if it’s sales value matches the MAX value for that state.  The IF statement is as follows:


IF {FIXED [State/Province], [Sub-Category] : SUM(Sales)} = [MAX SUB & STATE FIXED]


Creating The Plot

Now if we drag that calculation onto a bar chart with Sub-Categories and the Distinct Count of State we will see the initial plot we are trying to recreate.  This plot is now showing us the amount of times each Sub-Category is matches the maximum value in a state.


The result we are looking for does not have the Null values included so we can filter them out and after adding labels we will a plot that looks just like the one we are trying to achieve:


Creating The Tool Tip

Now that we have our plot, the last step is creating the hover over tool tip map. To do this we will first need to create a new sheet.  Then drag our Longitude and Latitude into the rows and columns, then Country and State/Province into the details.  Now to colour the States that have the selected Sub-Category as their top Sub-Category we need to create a calculated field.  This calculation will return a True or False if the Sub-Category selected matches the maximum Sub-Category in that state:


[Max Sales by State and Sub] = [Sub-Category]


By dragging this calculated field onto the colour we will see the States that have the selected Sub-Category as their most popular highlighted:



Finally, we will add this to our tool tip of the original bar chart and we’re done!  To do this we will go to the bar plot sheet and click on tool tip on the marks card.  From there, click on the Insert button in the top right corner, then Sheets, then select the sheet with the map on it.




And that’s it!  You will now have a bar chart that shows the most popular Sub-Categories and in how many states they are most popular.  When you hover over a Sub-Category it will also show a map with the states it is the top Sub-Category in highlighted.  There may be some formatting needed to completely match the example such as colour, title and tooltip wording changes however the functionality and visualization is all there.



I hope you had fun following along, and perhaps even learnt something.  You can access the original Work Out Wednesday page at the link below.

Thanks for checking out my blog!

#WOW2023 W31: Can you find the most popular sub-category by sales per state?




Mikael Nuutinen
Author: Mikael Nuutinen