Posted by Adam Jacobs

January 15, 2018

11:26

Leave Reply

Tableau Master Challenge #4

Many Tableau users are familiar with measure swapping, an approach to allow increased dashboard flexibility. Users can select metrics as if they were dimensions of the data, when the metrics are in fact columns in a data source. This is achieved with a combination of text parameters in a list, and calculated fields using the CASE statement to generate a list of measures.

But what happens when the metrics have different formatting requirements? This is a common problem, when users are switching between financial data, counts and percentages. We get some imperfections where customers counts are represented by decimals due to “automatic” number formatting:

Or ratios are shown as decimals instead of percentages:

We would like to see a dashboard where different metrics have different formats. To attempt this Master Challenge, use the Superstore sample data that ships with Tableau. To solve this challenge, we must allow the user to select a single measure from the list of available measures, and see the correct format reflected immediately. In addition, the appearance should be seamless, with no breaks or carriage returns in the labels or tooltips.

To align with the solution, create new measures with these calculated fields:

  • Customers: COUNTD( [Customer ID] )
  • Order Value: SUM( [Sales] ) / [Orders]
  • Orders: COUNTD( [Order ID] )
  • Orders Per Customer: [Orders] / [Customers]
  • Products: COUNTD( [Product ID] )
  • Products Per Order: AVG( { FIXED [Order ID] : COUNTD( [Product ID] ) } )
  • Profit Ratio: SUM( [Profit] ) / SUM( [Sales] )

And create a parameter called “Measure to Show” with these values:

And create a CASE statement to parse the parameter:

CASE [Measure to Show]

WHEN “Average Discount” THEN AVG( [Discount] )

WHEN “Average Orders per Customer” THEN [Orders per Customer]

WHEN “Average Product Quantity” THEN AVG( [Quantity] )

WHEN “Average Order Value” THEN [Order Value]

WHEN “Products per Order” THEN [Products per Order]

WHEN “Profit Ratio” THEN [Profit Ratio]

WHEN “Total Customers” THEN [Customers]

WHEN “Total Orders” THEN [Orders]

WHEN “Total Products” THEN [Products]

WHEN “Total Profit” THEN SUM( [Profit] )

WHEN “Total Sales” THEN SUM( [Sales] )

END

There are five types of formatting we’d like to include:

  1. Currency in dollars (average order value)
  2. Currency in thousands of dollars (total sales and profit)
  3. Decimal number (orders per customer, average product quantity, products per order)
  4. Whole number (customers, orders, products)
  5. Percentage (average discount, profit ratio)

If you assemble all the pieces correctly, you should arrive at the following results:

Good luck! Click here for a workbook and walkthrough of our approach.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Explore Posts By Category

Archives

Archives

Want to know more?

Contact us