Jun 13, 2022

Truth is multidimensional and depends on the perspective you view from

Just as your Power BI table visualizations.

I often want to build a dashboard with 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 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

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 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, charts or tables.