Animated Tooltips in Power BI

Tooltips are always good when you wish to keep your report viewers focused on a report by providing extra information on the same page through mouse over. Although default tooltip and custom tooltips are good for showing static information, you sometimes need to show animated content like trends and behaviors based on historic data.

Recently I did an experiment with tooltips by incorporating animated content. The experiment was fairly successful as lot of viewers on LinkedIn viewed my post and requested details about how this was implemented. For those who wish to experiment tooltip I have decided to pen down steps to help enthusiasts and professionals to try animated tooltips in their reports.

Mall Traffic Image

Click here to see report in action!

For this example I have created a sample report with fictitious data to show mall traffic. Nowadays, lot of tools are available to capture mall traffic with intelligent cameras and applications to recognize type of visitors. This example assumes that data file is having records of mall visitors bifurcated based on gender and age.

I have used calculated columns and measures to create dynamic titles of visuals to behave according to the selected content. A source pbix file is also included to enhance this example further.

Visitors were classified through following table. Unicode characters were used to show respective icons.

Table: Classification

Classification Unichar
Infants 128118
Boys 128102
Girls 128103
Men 128104
Women 128105
Cars 128662

 

Mall Traffic is shown in following table assuming that data is classified based on visitor type. Additionally a column is added to show traffic in terms of cars parked in the mall parking area. This kind of example may help mall management in identifying peak parking months.

Table: Mall Traffic (Note: this is not a full list)

Year Month Boys Girls Men Women Infants Cars
2016 1 23000 19000 34000 39000 5000 800
2016 2 18900 18000 40000 42000 7000 1100
2016 3 14800 17000 46000 45000 9000 1200
2016 4 10700 16000 52000 48000 11000 1450
2016 5 6600 15000 58000 51000 13000 900
2016 6 11000 14000 64000 54000 15000 1000
2016 7 21000 17000 59000 49000 14000 1100
2016 8 31000 20000 54000 44000 13000 670
2016 9 41000 23000 49000 39000 12000 800
2016 10 23000 19000 34000 39000 5000 930
2016 11 18900 18000 40000 42000 7000 1060
2016 12 19000 22000 46000 45000 9000 1190
2017 1 10700 16000 52000 48000 11000 1185
2017 2 21000 15000 58000 51000 13000 1180
2017 3 11000 14000 64000 54000 15000 1175
2017 4 21000 17000 59000 49000 14000 1170
2017 5 31000 20000 54000 44000 13000 1165
2017 6 41000 23000 49000 39000 12000 1160
2017 7 11000 15000 58000 51000 13000 1200
2017 8 11000 14000 64000 54000 15000 1215
2017 9 21000 17000 59000 49000 14000 1230
2017 10 31000 20000 54000 44000 13000 1245
2017 11 41000 23000 49000 39000 12000 1260
2017 12 23000 19000 34000 39000 5000 1000

 Model Diagram

Model Diagram below is showing relationship between two tables used. I have used additional columns and measures to perform analysis. Tables were transformed through edit query to make it analysis friendly. You may find query editing steps in sample pbix file.

Mall Traffic

 Play Axis Visual

I have used “Play Axis” custom visual to create animation on tooltip pages (You can import the same from the marketplace).  It was deliberately kept behind the clustered bar chart to work in background. “Year” column was selected in the field well to run tooltip based on years. In other tooltips, I have also used visitors’ classifications for animation.

PlayAxis Tooltip

Following settings were done in the format section of “Play Axis”.

PlayAxis Tooltip2

Calculated Columns and Measures

Following calculated columns were used for different reasons.

Calculated columns for creating icons

UnicharIcon = UNICHAR(Classification[Unichar]) 

Measures used for dynamic titles of different visuals and tooltips. These measures show values based on selections made through “Play Axis”.
AllYears = CONCATENATEX(VALUES(MallTraffic[Year]),MallTraffic[Year],” | “, MallTraffic[Year])
AllClassifications = CONCATENATEX(VALUES(MallTraffic[Icon]),MallTraffic[Icon], “| “)
AllClass = CONCATENATEX(VALUES(MallTraffic[VisitorClassification]),MallTraffic[VisitorClassification],” | “)
How to see animated tooltips.
Following visuals (marked with red arrows below) were configured for tooltips. You can check with mouseover to see tooltips in action. Keep holding your mouse to see animation effects. For cards, you have to keep your mouse over the figures and for line chart, keep mouse over the required node.
tooltips
This document is not covering method for creating tooltips as this is assumed that you are aware of the normal procedure of creating tooltip pages.
Following picture indicate how you can associate tooltip with your visual. However, you have to be sure that the field(s) used in your visuals are used in your tooltips otherwise it will not work.
tooltipsconfig
You may download complete files including sample Excel file from the following link.
Keep following my blog for more exciting examples.

Fuzzy Matching / Merge – Power BI

A new video is recently added by EZPowerBI.com to explain Fuzzy Matching / Merge feature of Power BI. This is understood that this feature is presently in preview stage but it is fairly important as there are lot many use cases where you have to merge data where you can expect variations in connecting table. This can be resolved through fuzzy merge logic implemented in Power BI. You cannot say that the accuracy is perfect but it will save you a lot from extra efforts needed to clean your data in such scenarios. This video is highly recommended for those who want to get hold of this feature at this stage. Subscribe to my YouTube Channel as well as this site to keep watching up to date contents.

Bookmarks in Power BI

Bookmarks are one of the most interesting features of Power BI with capabilities of making your life easier when you want to capture different combination of selections. This feature help you create stories in well organized manner.

I have recorded a video session giving quick overview of Bookmarks along with the example to help early adopters understand and implement this feature.

Click on link below to watch this video.

EzPowerBI.com is actively involved in promoting Power BI through its blog and videos. Keep following us and subscribe to our Youtube Channel.

Append and Merge Tables in Power BI

There are scenarios where different departments are working separately and sending data to a central location for consolidation. Organizations where data governance is taken seriously, there are less efforts required in combining such data. However, in scenarios where there is considerable variance/difference between source files, it is difficult to append or merge multiple files.

This blog post is sharing basic features available with Power BI to tackle such scenarios. In this example we will use a sample excel file having necessary tables involved. Download Sample Excel File


Appending Tables

For Example related to append, following tables will be used.

List A

Code Item Price
1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11

List B

Code Item Price
1 Pencil 2
2 Eraser 3
5 Marker 12
6 Whiteboard 45

Step 1

Open a Power BI desktop file and click on “Get Data” icon from the ribbon. Select “Excel” and paste following URL in filename section.

https://ezpowerbi.files.wordpress.com/2018/07/ezpowerbi-com-merge-and-append-sample-data-012018.xlsx

Get Data

Note: If you have downloaded this file, you can select file from your folder.

Step 2

Select checkboxes of “List A” and “List B” from the list and click “Edit”

Step 3

You will notice that Power BI Query Editor window will open after previous step.

Note: If for any reason this window is not opening directly, then you have to click on “Edit Query” icon on the ribbon.

Step 4

Right click on Query Pane and select as follows
New Query>Combine>Append Queries as New

EZPowerBI.com Append Queries - 1

Step 5

In order to append files, you need to select following files as mentioned below. Since in our example, we are using only two tables, multiple tables can be selected if second option “Three or more tables” is clicked. Press “OK” when correct files are selected.

Append files

Step 6

You will notice that now both tables are combined and showing collective list.

1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11
1 Pencil 2
2 Eraser 3
5 Marker 12
6 Whiteboard 45

However this list has duplicate values. These were purposely entered in the list to show you how we can get rid of those.

Step 7

Click on “Remove Rows” icon from the ribbon and select “Remove Duplicates”. This action will give you final list with unique items from both lists.

Remove Duplicates

This will complete your append operation.

Final list will be like below.

1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11
5 Marker 12
6 Whiteboard 45

Step 8

You can rename new combined table as “Combined_Appended” for this example.

Step 9

Click on “Close and Apply” icon on the ribbon to complete this activity.


Merging Tables

Merging of tables is required when you are combining different tables with some common columns to make use of required columns of other tables. This is helpful in situations where product specification is maintained in one table while product prices are maintained in another. Following tables are used to this scenario.

1- Combined_Appended

2- List C

To import List C table, follow similar steps like mentioned in Step 1 of previous example (Append Tables).

Get Data

Step 2

Select checkbox of “List C” from the list and click “Edit”

Step 3

You will notice that Power BI Query Editor window will open after previous step.

Note: If for any reason this window is not opening directly, then you have to click on “Edit Query” icon on the ribbon.

Step 4

Right click on Query Pane and select as follows
New Query>Combine>Merge Queries as New

merge

Step 5

Now select files as demonstrated below. After selecting files click on “Code” column in both tables. This will make selected columns gray to identify columns used for merging tables.

Merge File Selection

Now click on “OK” to confirm merging.

Step 6

Once merged table is loaded, click on the icon on the top right corner as mentioned below to expand table columns.

expand

Step 7

You will see following window like below after completing previous step.

Select Columns

Now select Columns not available in first table to avoid duplicates. I our case we will select only “Box” and “Units” columns. In this example we can deselect “Use original column name as prefix” checkbox.

Select Columns2

Click on “OK” to complete this step.

Step 8

You will now see a merged table with combined columns like below.

merged file final

Step 9

Click on “Close and Apply” icon on the ribbon to complete this activity.


 

 

Data Spadework for Power BI Visualization

powerbi

If you have decided to work on a report for your organization or customer, make sure that your spadework is up to the mark. This will help you in saving lot of hassle when making a report or visualization when making without proper spadework.
First of all you have to see what type of control you have on your data source.

1- Full Control
This must not be taken wrongly as this is related to access to data, here it means that how much you can influence the way and form you are getting data for your reports. If you are mandated to implement business intelligence in your company, then you have to set standards for your data. This may fall in data governance domain but to some extent you have to play a role to get data in meaningful format without unnecessary and inconsistent arrangements. This may be very cumbersome to check for the sanity of data after imported in Power BI especially when your data is in bad shape. This may adversely affect your facts and figures and may contribute in raising false flags due to incorrect data. If you have full control on your data then you can easily achieve maximum accuracy level in your reports.

2- Partial Control
When you don’t have much of a control on the way of getting data, you might have to face a lot of challenges in producing quality results. This may be true when organizations are not ready to change the way the data is gathered and produced. This also belongs to change management where organizations are reluctant to experiment new ways because of working culture and limitations in terms of workforce capabilities. In order to minimize effects of inconsistent data format, you need to list down possible variations to cater the same in your queries. Things may be very difficult in cases where consolidation of data is needed. The best way of getting good reports is to ensure that each data provider is ensuring consistent data entry. Proper standards can be enforced when referring similar data like department names and designations where consistent data entry can help in getting correct figures when data is consolidated from different sources.

3- No Control
In cases where you have simply no control on data sources, you have to set expectations correctly with the recipient of these reports. There are always chances of errors especially when data consolidation is required and data is constantly refreshed and updated. Such cases requires constant testing and maintenance if report is not a one off report. Whenever fresh data is imported, proper auditing is needed to check if any real data is ignored by your query rules. This is also true when your report needs data from external/third party sources. If your external source has changed the format of data or moved to a different URL, you will have to go back and fix your reports.