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

Unlocking Microsoft Fabric: A Simple Guide when you only have a personal account.

-
Image
" You cannot create a Microsoft capacity using a personal account. Use your organizational account instead." What a disappointment and frustration! I've been trying to set up Microsoft Fabric for a while now and figure out how to work around this error. I use my personal Gmail for an MVP subscription to learn and experiment with Azure services. When I visit app.fabric.microsoft.com and try to use my private Gmail address, I keep getting a similar message: It's frustrating because I'm eager to dive into Fabric and explore its capabilities. I would get the same error trying to set up Azure Data Catalog, PowerBI embedded, and more. What are the different account types and how do they differ? A work or school account is created through Active Directory or other cloud directory services, such as Microsoft 365. On the other hand, a personal account is one that's manually created for individual use, consisting simply of a username and password. After digging into ...
Read more

ETL to ELT journey: Break free your Transformations and discover Happiness and Data Zen

-
Every data integration pipeline consists of 3 stages: Data Extraction (E), Data Transformation (T) and Data Load (L) During the  Data Extraction stage, the source data is read from its origins: transactional databases, CRM or ERP systems or through data scraping from web pages. During the  Data Transformation stage, the necessary modifications are applied to the source data. This includes data filtering, enrichment or merging with existing or other source datasets, data obfuscation, dataset structure alignment or validation, fields renaming and data structuring, according to the canonical data warehouse model.  During the Data Loading stage, the data is stored in the pipeline destination, which could be a staging area, data lake or data warehouse. There are two principal methods for the data integration process: transferring it from where it originated to the destination, where the data will be used for analysis, ETL and ELT. The difference between ETL and ELT pipelines...
Read more

Coding is a rollercoaster of efficiency and eyebrow-raising discoveries.

-
Image
Data Engineers or Developers - many of us love to be gourmet chefs in the kitchen. When it comes to planning and design, we would rather throw all ingredients in the pot and see how it comes out.  Coding without a plan is like assembling a puzzle in a dark room. The result will most probably be unexpected and off the canvas. Whether you follow Waterfall or Agile development strategy, planning and design phases are non-negotiable and are essential to reduce development cycles and redo work. Once upon a time, one data engineer created an amazing piece-of-cake automation pipeline. This masterpiece had very complex logic, pulling data from multiple sources, and merging and persisting the data in a complex, incremental way. When the pipeline started to run successfully and automation flows worked, the data engineer got very excited and considered this development done. A few days later QA engineer found out that the result dataset was never created in the destination. Why has that ...
Read more