Monday, May 11, 2020

Tableau - How to color top N bars sorted by measure by sort order?

1. What to Achieve?


Here we will build a bar graph with multiple measures in the view such as Measure 1, Measure 2 and Measure 3, as shown in the below diagram. In this view, you can choose which measure to be sorted by, and you can specify the sort order as well, either ascending or descending in this example. Moreover, only the top N bars of the selected measure are highlighted with a different color.

2. How to Build?


2.1 Create Parameters


In this exercise, we create 3 parameters, [Sort by], [Order by] and [Top N]. 
[Sort by] is used to select a measure that the bars are sorted by. 
[Order by] is used for sort order selection.
[Top N] is used to determine how many bars on the top will be highlighted.
The definitions of the 3 parameters are listed up as below.

[Sort by]
    Data Type: String
    Value:
        Measure 1
        Measure 2
        Measure 3

[Order by]
    Data Type: String
    Value:
        Ascending
        Descending

[Top N]
    Data Type:  Integer
    Minimum:  1
    Maximum: 10
    Step Size:   1

2.2 Create Calculated Fields


The 2 calculated fields are created as following.
[selectedMeasure] is used to get the selected measure in response to parameter [Sort by].
[fieldForSort] is the field used for ordering. When you choose "Ascending", use [selectedMeasure] as the order field; on the contrary use -[selectedMeasure] when you select "Descending".

[selectedMeasure]
    CASE [Sort by]
        WHEN "Measure 1" THEN [Measure 1]
        WHEN "Measure 2" THEN [Measure 2]
        WHEN "Measure 3" THEN [Measure 3]
        ELSE NULL
    END

[fieldForSort]
    IIF([Order by]="Ascending", [selectedMeasure], -[selectedMeasure])

[Rank]
    INDEX()

2.3 Create Set


[setTopN] set is created on field [Product], with [fieldForSort] as the sort field. Parameter [Top N] is used to decide how many bars on the top of the graph will be included in this set.


2.4 Create Color Fields


Considering only the selected measure will be highlighted, here we create a separate color field for each measure, respectively. They will be placed on the color mark for every measure in the view. 

colorTopNm1
    IIF([setTopN] AND [Sort by]="Measure 1", 1, 0)

colorTopNm2
    IIF([setTopN] AND [Sort by]="Measure 2", 1, 0)

colorTopNm3
    IIF([setTopN] AND [Sort by]="Measure 3", 1, 0)

2.5 Drag and Drop Fields onto the View


Now it is time to put all the fields on the right place.
Columns: [Measure 1], [Measure 2], [Measure 3]
Rows:      [Rank], [Product]
Color Mark for [Measure 1]: [colorTopNm1]
Color Mark for [Measure 2]: [colorTopNm2]
Color Mark for [Measure 3]: [colorTopNm3]


For [Product] on the Rows card, use the Sort setting as shown by the below diagram.


3. One Note


Although Tableau offers a bunch of guidelines for performance tuning including how to write calculations, performance is not of concern in this example.

No comments:

Post a Comment

React - Makeover in React: W3Schools How To

When it comes to building a React app, I get used to searching for a UI library to build the front pages, a straightforward way to achieve f...