Skip to main content

How to show Dynamic Top N and Bottom N View at the same worksheet?

Do you ever face with the request from the customer: I need to see who is in the top and who is in the bottom in the same list? I do. This topic will explain how you can cope with this request.


 Data Source

Dashboard is built on the data by Car Insurance Comparison.  If you want to pump up as a Tableau expert I strictly recommend you to use the data from Makeovermonday.co.uk/data/ to build interesting visualisations with profound data analysis.

My dashboard shows which States are safety and which are the most dangerous according to the number of car accidents. The final rating is compiled on the basis of several reasons for which a car accident can occur:
  • Careless driving
  • Drunk driving
  • Failure to obey
  • Speeding
Map shows how many accidents were in each state depending on the selected type of car accident's reason. Colour code of state indicates number of car accidents in the state. If you hover on any state you can see final score by Dangerous Driving Rating. 

In the lower part of dashboard you can see three metrics which indicates Careless driving (Pedacyclist fatality rate,%), Drunk driving (Fatalities w/BAC=0.8+) and Failure to obey (Crashes when seat belt not used,%). These charts shows by default top 10 dangerous States in terms of car driving. You can easily see top 10 the most safety  States by selecting 'Top safety' in the appropriate filter. 

This approach allows to place data in a compact format, avoiding to creare a separate chart with Top N and Top Bottom. 

How to make dynamic Top ('Top Dangerous') and Bottom ('Top Safety') in the same chart?

Step 1
         
Before creating a calculated field, let's make a Parameter, that will allow users to quickly toggle between views with different variables. Thus we will make dynamic Top/Bottom number of States depending on the selected parameter value.



Step 2

Create calculated field as demonstrated below.

This calculation allows to rank the data according to the value in chart (in the example 'Pedacyclist Fatality rate' measure value).

Step 3

Create 'Top/Bottom?' calculated field as demonstrated below


Pay attention: Maybe you are wondering why I created a separate Rank calculated field, when I could not create it by writing  an Index() in the 'Top/Bottom?' calculation. I have made it because there are many other cases when this calculation will help you too. I will explain different cases in further posts.

Step 4

Put Rank Calculation in the row shelf and select  in the Editor of table calculation 'Table Down'. Sort list of States by value (in the example 'Pedacyclist Fatality rate' measure value). Add 'Top/Bottom?' field in the Filters shelf and select Top (Top Dangerous) or Bottom (Top Safety). Add dynamic Title in you chart by adding 'Top/Bottom?' measure value.



Comments

  1. tradeimexinfo12 July 2021 at 09:17

    The information in the post you posted here is useful because it contains some of the best information available. Thanks for sharing it. Keep up the good work shipment data

    ReplyDelete
  2. Joseph29 June 2022 at 20:12

    This comment has been removed by the author.

    ReplyDelete
  3. Joseph29 June 2022 at 20:13

    https://www.inetsoft.com offers an option to export the report in PDF or XML formats. It is one of the best web based reporting tools which allows you to view the reports in XML format.

    ReplyDelete

Post a Comment

Popular posts from this blog

Performance Troubleshooting: Tabular Model in Power BI

For the last 2 years, I have been using Power BI as one of the core tools to provide different data insights for the top management.  Together with my great team, I was constantly improving our key reporting tools. As in a classical way we were facing problems with the performance. The rising complexity reflects on the user's response and on the stability of report updates. The Tabular model allows boosting both the performance and the speed of development of massive reports with complicated data models and too many DAX.   Let us consider the use case and different ways of improving report performance including usage of the Tabular model. About DataSet:   Classical OLAP schema - Snowflake  Size of fact table - over 40 mln rows Over 40 dimensional tables Data Connectivity Mode - Import Type of Connector - SQL Server Database The report was hosted in Power BI Report Server.  Ways of investigation and steps to improve the performance: Migrate report from on-premise PBI RS to the Cloud
17 comments
Read more

Top the fastest growing companies in the United States

Hi there, I was lucky to work with  such BI tool as Tableau for some time and here I would like to share with you my experience in Tableau features and data preparation. Here is presented comparison analytics by US states. DataSource The first data set I took from Tableau Sample Data Sets . My datasource contains some data by top fastest growing private companies in the United States: revenue, number of  workers, info by location and so on.  The larger amount of data, the more you can make various analysis of data set. I was wondering about: Where are the most successfull companies accumulated? How much is dispersion between states in a matter of number of employees and profit among states? Which industries are most developed in different states? 'How to use' and Description of Dashboard Box plot allows to see the median and outlier or in other words who is the outsider or leader in terms of profitability by states. To see which industries have succe
10 comments
Read more

Transforming data into action in Healthcare sector

Transforming data into action allows to save the lives of people. Occasionally a simple report that you can do by a couple of hours can become a powerfull tool for raising important problems and determining the course of action. So it happened with the malaria's data by Zambia. I can not turn a blind eye to the world's problem (especially when it's related to the life of children) so I've decided to create a report. The more we talk about it, the more chance to draw attention to this problem - there is more chance to save children's lives and hope for the future.  Data analysis is a sort of journalistic investigation in the course of which you can find a lot of interesting facts. This topic is not an exception and that's what I've find out: Eight visionary companies created a stack of technology solutions that allowed health workers  quickly access reliable data and make informed decisions  Since 2014 malaria starts rapidly decrease aft
36 comments
Read more