Truth is multidimensional and depends on the perspective you observe it from

-

Just as your Power BI visualizations.

I often want to build a dashboard with some sleek elegant visualizations and at the same time show as much data as possible. 

Tooltip is a great feature when you want to show additional data without making visualizations huge and cumbersome. Charts in Power BI have out-of-the-box boring text tooltips. 


But if we want to add a tooltip to Tables and Matrix visuals, the process is not straightforward.


I have recently figured out how to build a custom tooltip in a Power BI and got so excited about it, that now I show it to just everyone willing to see it. 



Today I will share it with you.


My favorite dataset

I will use a yellow taxi dataset. This free dataset contains billions of trips in New York City for the last 10 years and is provided by the NYC Taxi and Limousine Commission (TLC). It contains information on the pickup and dropoff locations, number of passengers, trip distance, fare amount, tip amount, etc.

Using Power BI, I am loading the January 2022 data from the URL:
https://s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2022-01.csv

I am using this PowerBI query to load the file from a web location and adjust datatypes:

let Source = Csv.Document(Web.Contents("https://s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2022-01.csv"),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"VendorID", Int64.Type}, {"tpep_pickup_datetime", type datetime}, {"tpep_dropoff_datetime", type datetime}, {"passenger_count", Int64.Type}, {"trip_distance", type number}, {"RatecodeID", Int64.Type}, {"store_and_fwd_flag", type text}, {"PULocationID", Int64.Type}, {"DOLocationID", Int64.Type}, {"payment_type", Int64.Type}, {"fare_amount", type number}, {"extra", type number}, {"mta_tax", type number}, {"tip_amount", type number}, {"tolls_amount", type number}, {"improvement_surcharge", type number}, {"total_amount", type number}, {"congestion_surcharge", type number}}) in #"Changed Type"


I will also load the pickup locations description table from https://s3.amazonaws.com/nyc-tlc/misc/taxi+_zone_lookup.csv


Table Example. 

I have put here a simple table showing tips and the total trip amount, depending on the trip distance. ( I have added a “tripDistanceBucket” column using the below IF construct )


TripDistanceBucket = IF ('yellow_tripdata_2022-01'[trip_distance] < 10 , "0m-10m", IF ('yellow_tripdata_2022-01'[trip_distance] >= 10 && 'yellow_tripdata_2022-01'[trip_distance] < 20 , "10m-20m", IF ('yellow_tripdata_2022-01'[trip_distance] >= 20 && 'yellow_tripdata_2022-01'[trip_distance] < 30 , "20m-30m", IF ('yellow_tripdata_2022-01'[trip_distance] >= 30 && 'yellow_tripdata_2022-01'[trip_distance] < 40 , "30m-40m", IF ('yellow_tripdata_2022-01'[trip_distance] >= 40 && 'yellow_tripdata_2022-01'[trip_distance] < 50 , "40m-50m", IF ('yellow_tripdata_2022-01'[trip_distance] >= 50 && 'yellow_tripdata_2022-01'[trip_distance] < 60 , "50m-60m", "60m and more") )))))



Now, I want to add more information in a tooltip

Here is how to build a tooltip in 3 simple steps:

First, I will add a new page and will change the page information to “Allow use as a tooltip”. I will call this page "Cell tooltip"


Next, to this new “tooltip” page, I will add 3 additional visualizations: the number of passengers by pickup location, the total number of passengers, and the total trips count.



Finally, I will return to the main page and choose “General” -> “tooltips”. 





The tooltip tab is supposed to show in the "General" -> "Page" dropdown.

All done At this point, when my mouse stops on the table cell, I will get additional information in a tooltip based on the cell where I am now. You can use the same tooltip page in any visualization, chart or table.




This feature allows us to create multi-dimensional tables and charts.

May all your visualisations make sense


Yours,
Maria

Comments

Post a Comment

Popular posts from this blog

SQL Awesomeness: Finding a QUALIFY query clause in the depths of the database ocean

-
Image
After so many years of writing SQL queries, I cannot believe I never came across a QUALIFY query clause. It feels like discovering a treasure in the depths of the database ocean. Even ChatGPT was not aware of it! QUALIFY query clause was invented by Teradata and is not a part of SQL Standart but apparently, multiple database vendors are supporting it, for instance, Snowflake and Databricks. QUALIFY clause solves the challenge when applying a window function as a query filter. SQL Standart and most database vendors will not allow to run of a window function as a part of a WHERE clause because window functions get evaluated after the HAVING clause. We typically create a CTE or subquery adding a window function to be able to filter on a it at a later query stage. Here is an example of how you can use QUALIFY to piece-of-cake filter on the window function.  I will use the Snowflake database, as an example. Let us consider that we have a list of queries and we need to find the last query ex
Read more

Look back and realize how far you came

-
Image
It appeared out of nowhere while I was having a strong argument with the ton of dust that had taken over my computer room. My former favourite book, "Advanced Transact-SQL for SQL server 2000" brought an upset look to my face, the one I usually get from our NetApp admin. He usually gives me such a look when I ask him for more disk space. At the time of SQL 2000 there were far less good SQL books available than today and amazing SQL programming tricks were living on Itzik’s book pages; and, similar to the Rubeus Hagrid’s “The Monster Book of Monsters” it attacked anyone who attempted to open it, unleashing wild and exciting brilliant ideas. Nowadays there are a lot of SQL books but not all of them are worth reading. Some of them should be strongly avoided and if you touch them you should immediately wash your hands and have a drink or three. A few month ago I was asked to technically review some new SQL Server book. Reading their missleading claims of how the optim
Read more

The Greatest Reasons to use or not to use a Centralized Data Access Architecture

-
Image
When developing a modern Data Platform Layer, one of the main decisions is whether to opt for centralized or decentralized data access architecture.  There is no “one-fits-all” solution, both have advantages and disadvantages. A Centralized Data Access Architecture would usually mean duplicating data from the operational layer into the analytical layer and applying various transformations to data to support and speed up data analytics. Operational online transaction processing Layer , where all microservices and their operational databases are located. Analytical Data Layer , where we would have data lakes that support data Scientists' work and a data warehouse, that supports Business Intelligence. Transformations, ETL or ELT data pipelines , which are moving data from the operational layer into the analytical layer. I f we opt for a Centralized Data Access architecture, what would be the benefits and the drawbacks? CONSISTENCY : consolidating data into a central location can
Read more