How to use Reference Lines in Table Calculations in Tableau

How to use Reference Lines in Table Calculations 



  •  In this example, we can see how to use Table Calculations with Logical calculations.
  •  Let’s start with finding out which ‘Category’ is reaching ‘Average Sales’ in each ‘State’. The Sales which cross the Average sales should show one color, say Blue and the Sales which do not reach the Average sales should show Orange color. Sample Superstore database and Tableau 10.3 is used.
  •  Pull Sales onto Columns and State, Category in Rows.
  •  Add a ‘Reference Line’. We can add it two ways. Use the Reference Line option from ‘Analytics Pane’ or Right click the ‘Sales axis’ and use option ‘Add Reference Line’. We can have multiple Reference lines in a single dashboard.
  •  To get different colors for the Sales which reach the Average and which do not, create a Calculated field, name it ‘Sales KPI’ (Key potential Indicator) and give the formula as ‘Sum(Sales) > Window_Avg(Sum[Sales])’.
  •  To know more about ‘Window functions’, if you take reference line across ‘pane’, each pane is considered as ‘each window’. (Here we have to compare each individual category sales with Category Sales(Avg) in each State).
  •  If you take reference line across ‘Table’, entire table is considered ‘one window’. (Here we have to compare each individual category sales with entire Category Sales(Avg) across the table).
  •  Pull the formula Sales KPI, onto colors and edit the colors for True to Blue and False values to Orange. Now the values satisfying the True Condition are in blue color and the values in False are in Orange.

  • There are three options on how to use the reference line Table, Pane and Cell.
  • If you Pull the Reference Line on ‘Table’, this shows the ‘Average of Sales’ for the ‘whole table’. We can even format the Lines, Fill Above and Fill Below with different colors to enhance the usability of Dashboard as in Fig below.
  •  If you pull it on ‘Pane’, it will show the Average of Sales for the individual pane, which is State in this example. To get this, change the ‘Compute Using’ from ‘Table (down)’ to ‘Pane (down)’ in ‘Sales KPI’ in ‘Marks section’ as in fig below.
  •  If you pull Reference Line on ‘Cell’, it will give average of sales on each Cell, which is category here. Pulling it on the on Cell, i.e. Category doesn’t make any sense here as the both will give the same number Sum(Sales) and Avg(Sales). But it will be useful in few scenarios where you want to check the ‘Average Profit’ for each ‘Sales’ cell. For this, pull the Profit on ‘Details’ component of ‘Marks’ card. Add a reference Line on Cell. Choose  Sum(Profit) in Value, Aggregation to Average, Label to Value and do the necessary formatting. You can see the Average Profit for individual Sales for each category in each state as shown in fig below.

Comments

Post a Comment

Popular posts from this blog

Types of Sorts

Image
Sorts Sorts are of different types, 1.      Simple Sort 2.      Complex sorts 3.      Advanced Sort using Calculated field 1.      Simple Sort ·        Pull Region onto Columns, Sub-Category onto Rows and Sales on the Text on Marks card. Database used is Sample Superstore and Tableau 10.3. ·        We can find the highest ‘Sales’ in each ‘Region’ by clicking on the ‘Sort’ Button beside each Region. Here the Sub-category ‘Phones’ has the ‘highest Sales’ in ‘East Region’. ·        Note: The default sort type is Descending. ·        We can sort the Sub-Category too, it will be ascending and Descending according to alphabetical order. ·        We can also sort the Sales ‘Row-Wise’, this give the sales from high to low Sales for that particular Sub-Category. ·        Now Pull Category onto Rows, Click on the Category, goto Sort, we can see the window as shown in the below pic. We have options for Sort Order Ascending, Descending. ·        Sort By : Da
Read more

HOW TO USE RANKS IN REAL TIME SCENARIOS IN TABLEAU

Image
HOW TO USE RANKS IN REAL TIME SCENARIOS   In this example, let’s find the Top 3 selling Sub-Categories in each State. We would be using Sample superstore database and Tableau 10.3. Pull Sales onto columns and State and Sub-category onto rows.   Create a Calculated field, call it ‘Rank’ and give ‘Index ()’. Index returns the row position. Pull the Rank onto Rows, in between State and Sub-Category. Change it from ‘Continuous’ to ‘Discrete’. You can find the Rank created, giving 1, 2,3 ... 656 across the whole table starting from Alabama to Wyoming.   Let’s change the Rank such that it gives Rank Only State wise. It should start giving Ranks from 1, 2,3...n in each state.   Right click on Rank, go to Compute Using, Choose the Option ‘Pane (Down)’ instead of ‘Table(Down)’. This action helps restart Rank (1 to n) in every State.       Now, Let’s give the Ranks 1 to N to Sub-Categories such that the highest sales holds 1 and next highest 2 ..  to do this, Right click on Rank, go
Read more
Tableau Desktop Vs Tableau Server Tableau Desktop:-   All your development is done here. From creating reports, charts,  formatting them, putting them together as a dashboard all the work is done on Tableau Desktop. 1. Data Source Connection 2. Extractions 3. Analysis Process 4. Statistical Analytics 5. Visual Analytics 6.Designing, Creating, Publishing and Scheduling Tableau Server:-   The dashboards you create are shared with other users using Tableau Server. When you upload(publish) a Dashboard to Tableau Server from Tableau Desktop, other users can access those Dashboards by logging on Tableau Server. 1. Data Source, Sheets/Workbook Management (Content) 2. User Management 3. Performance Management (Load balancing, Server Management) 4. Scheduling Activities What is the difference between core-based and user-based licenses 1 User-Based Server License:    If your Server Software license is designated as User-Based, the Named Author
Read more