At data school, each week presents an opportunity for us to delve deeper into our training learnings. In the end of Week 3, we were tasked with taking on the Workout Wednesday challenge of our choice and presenting our solutions. Among the myriad challenges available, I found myself drawn to one that centered around Level of Detail (LOD) expressions in Tableau. Having explored the power and versatility of LOD expressions previously, I was eager to apply them to a real-world business case: finding successful products and understanding their profit ratios. If you’re new to LOD, I’d written a introductory blog about it which you can read here.

This Workout Wednesday is from 2022 Week-4. The challenge I selected is a two-fold endeavor, as depicted in the screenshot below. The first half revolves around calculating the percentage of successful products within each category and sub-category. However, the definition of “success” here is nuanced. A product is considered successful only if it has been profitable for each of the four years spanning from 2019 to 2021. The second half of the challenge involves determining the profit ratio for the same category and sub-category.

Visualizing the solution for the first part of the challenge using a bar chart to display the percentage of successful products is simple. However, the real complexity lies in crafting a dumbbell chart for the second part of the challenge. A dumbbell chart, true to its name, features circular endpoints representing the maximum and minimum values of the profit ratio, with a connecting line showcasing the difference between the two.

Now, let’s delve into the intricacies of tackling this challenge head-on.

To begin, ensure that you have the Sample Superstore dataset from 2021 loaded into Tableau. For this LOD challenge, we’ll be employing Nested LOD expressions along with conditional expressions using IF-ELSE statements.

Setting Up the Visualization:

    • Drag the “Category” and “Sub-category” dimensions to the Columns shelf.
    • Next, let’s visualize what constitutes success. Drag “Product ID” and “Year (Order Date)” as discrete fields to the Columns shelf. And, drag Profit from measures to the view.

 

 

Defining Success with LOD Expressions:

      • Now, we’ll create a calculated field to label each product as successful or unsuccessful. Fixing in product ID and year level, we will find out unsuccessful products based on sum of profit less than zero and count of year is less than four.
        • { FIXED [Product ID]:
          IF
          min({ FIXED [Product ID],DATEPART(‘year’, [Order Date]):SUM([Profit])})<0
          or COUNTD(DATEPART(‘year’, [Order Date]))<4
          then “Unsuccessful” else “Successful” END
          }
        • This is going to be our base calculation; lets call it Success Label upon which percentage of successful products and profit ratio will be calculated.

Calculating the percent of successful products:

    • Now, let’s calculate the percentage of successful products relative to all products within each sub-category. We’ll use a Fixed LOD expression to ensure our calculation remains fixed on the sub-category view.
    • Create a new calculated field named “Percent Successful Products.”
    • Input the following formula:
      { FIXED [Sub-Category] : COUNTD(IF [Success Label] = "Successful" THEN [Product ID] END) }
      /
      { FIXED [Sub-Category] : COUNTD([Product ID]) }
    • This calculation divides the count of successful products by the total count of products within each sub-category, yielding the percentage of successful products.

 

Visualizing the Percentage of Successful Products:

    • Drag the “Percent Successful Products” calculation to the Rows shelf.
    • This creates a bar chart showing the percentage of successful products within each sub-category.
    • Format the chart for clarity and aesthetics, adjusting axis ranges, colors, and labels as needed.

Now, on to the second half of the challenge, creating dumbbell chart for profit ratio.

Create a Calculated Field:

    • Navigate to the Calculated Fields pane and click on “Create Calculated Field.”
    • Enter a name for the calculated field, such as “Profit Ratio – Successful.”
    • Input the following formula:
      { FIXED [Sub-Category] : SUM(IF [Success Label] = "Successful" THEN [Profit] END) }
      /
      { FIXED [Sub-Category] : SUM(IF [Success Label] = "Successful" THEN [Sales] END) }
    • This calculation sums up the profits for only the successful products within each sub-category and then divides by the sum of sales to obtain the profit ratio for successful products.
    • Repeat this step for the new calculated field named “Profit Ratio – Unsuccessful.”

Visualize the Profit Ratio:

 

 

  1. Drag a profit ratio to the Rows and bring another profit ratio to the same axis. This will give us bar chart with both ratios. This is because in order to create dumbbell shape we need to have both values in one chart.
  2. Drag the “Measure Names” field from Columns to the Details shelf in the Mark card. Change the mark type from Automatic to Circle to represent the profit ratios as circular endpoints.
  3. Now, we need to calculate the difference between these two values in order to show the line between these two circle. Drag one of the ratio to column shelf again and we will do calculation on the mark card itself. Pressing Ctrl, drag the sum of profit ratio of successful products and write minus sign ‘-‘ and then drag another ratio for unsuccessful products.
  4. Next steps are just few tricks to show the line such as, on the same mark card changing from automatic to Gnatt chart to bring the line shape and dragging this to the Size. Please also follow following gif tutorial for ease.
  5. Finally, dual axis the measure values and the difference chart and synchronize the axis to get the dumbbell chart. And, it’s just the matter of formatting and refining the visualization to achieve the final appearance of the challenge!

I learnt a lot about LODs through this challenge and I hope you found this journey as interesting as I did. Thanks for joining me on this adventure, and I look forward to sharing more Tableau tips and tricks with you in the future.

Till then, keep on learning and growing!

 

#tableauchallenge #dataanalysis

Prerana Amatya
Author: Prerana Amatya