Visual Resources to IMPRESS: Tooltip + Drill Down + Drill-Through

Leonardo Karpinski

Leonardo Karpinski

Power BI Master and Microsoft Certified Trainer, owner of the Power BI Experience. Graduated more than 20,000 students and participated in extensive projects for global companies.

Hi there! How are you?!

Today’s content will show you how to use some amazing visual features of Power BI ! We will give practical examples of how to use:

  • Tooltip;
  • Drill Down and
  • Drill-Through

These three resources serve to provide extra information about something and detail its analysis.

Database

To exemplify the use of each of the resources I mentioned, I will use an Excel file with 3 tables: a fact table called “Vendas” and two dimension tables “Clientes” e “Produtos”.

The Sales table has all the sales events of each product.

Figure 1: Sales table (fVendas)

The customer table is the traditional one. It has the columns idCliente (unique code for each client, “Cliente” (name) e “Tipo” (classification).

Figure 2: Customers Table (dClientes)

The “Produtos” table, beyond common columns (“Id”, “NomeProduto”, etc) will also have a column with the URL with the image of each product.

Figure 3: Products table (dProdutos)

Now that I have presented all the tables we will use, just import them into Power BI, ok?!

Relationships

Well, since the database doesn’t have a date table, let’s create it in DAX:

dCalendario =
CALENDARAUTO ()

See what created a table with the dates based on the maximum and minimum date was found (among all the tables we import). It was also created a hierarchy of “Ano”, “Trimestre”, “Mês” and “Dia” automatically.

Figure 4: dCalendario

You can now create the relationship between the tables. The modeling is of the StarSchema type, see how the relationships turned out:

Figure 5: One-way relationship of the dimension tables for the fact table


Drill Downs and Drill Up

Let’s calculate the invoicing with the sales? We will use a sum iterator function – the SUMX. The iterator functions have this “x” at the end..

Faturamento =
SUMX (
    fVendas,
    fVendas[Quantidade]
        RELATED ( dProduto[ValorUnitario] )
)

By using SUMX you are making calculations row by row of the table. Then for each row you are multiplying the “valor unitário” by the “quantidade”. Note that “ValorUnitario” isn’t in the fact table (fVendas), but in the dimension table, so the RELATED function was also used so that you can use a value from another table “(Produto)” within the function SUMX. See that the first argument of SUMX is the table where you do the calculation: “fVendas”. Then the second argument is expected to be a column of the same table. When it is not, we need to use the RELATED function to “bring” this value to the “Vendas” table through the existing relationship “(IdProduto)” ?! How are we doing so far?!

Likewise, let’s calculate the “Custo”:

Custo =
SUMX (
    fVendas,
    fVendas[Quantidade]
        RELATED ( dProduto[CustoUnitario] )
)

We will create two cards to represent the “faturamento” total and “custo”. To do this, simply select the “Card” visual and drag the measurement to “Campos”, look:

Figure 6: Creating Cards

Repeat the same procedure to create the card with the “Custo”.

The next step will be to create an area graph to show the “Faturamento” over time. To do this, just select the “Area Graph” view and drag the “Faturamento” measure to the “Valores” field and Date to the Axis field.

Remember that when we created the dCalendario table, it brought us not only our dates but a hierarchy? When we drag “Date” to the chart, we take the whole hierarchy along too. Drill Down will be responsible for navigating these hierarchies.

Tip:
Drill down: makes it possible to advance at different levels of a hierarchy and to detail the analysis within the same level, usually within the same dimension.


There are four buttons on top of the graph that allow this navigation between the hierarchies but there is a difference between them:

Figure 7: Difference between navigation buttons between hierarchies

Let’s see what happens with the last button (the “little fork”):

Figure 8: Fourth button

Notice that the graph was showing more points as you navigated from the least granular to the most granular level (more detailed) in the hierarchy. So the first time I pressed the button, the axis showed beyond the “Ano”, the quarter. Then it advanced to “Mês” and then to “Dia”. The X axis showed these fields being “concatenated”. Briefly we advanced of level respecting the previous level.

The third button (double arrow down) no longer respects the previous level because it only shows the current level. Look:

Figure 9: Third button

See that when we arrived at the “dia” level, it was not shown in which month or quarter it is being considered, in other words, it does not consider the previous levels.

Figure 10: Second button

The last level advanced option is the second button (single down arrow). It is used to filter specific points on your chart. It asks you to select a point on the graph to advance the level at that specific point. As you do this, notice that your graph is being filtered and therefore may affect other visuals on your page – see that the value of the card changes when we advance in level.

Figure 11: Second button

Finally the first button (single arrow up). It just returns to the previous level (undoes the level advance you made). Look:

Figure 12: First button (Drill up)

Pretty nice, huh? Let’s see the next resource!

Tooltip

Before we start, what it Tooltip ?

Tip:
Tooltip: allows you to display extra information that is relevant to the analysis, usually crossing information from other dimensions

First, let’s create a vertical bar graph. To do this, just select the view Clustered Bar Chart and drag the measure “Faturamento” and the column “NomeProduto” to these fields:

Figure 13: Creating bar graph

Now you will need a new page to use as Tooltip. Remember to right-click on the name of this page to leave it hidden because we don’t want the user of the report to get to it, ok?!

Figure 14: Hiding Tooltip page

Now follow these stages:

Figure 15: Setting up the tooltip page
Stages:
1. In this new page you created, look for "Informações da Página" and activate "Dica de Ferramenta".
2. Look for "Tamanho da Página" and select "Tipo -Personalizar". Choose the desired width and height.
3. Go to the top menu of Power BI at "ExibiçãoExibição da Página" and choose "Tamanho Real".
4. Go to "Alinhamento de Página" → "Alinhamento Vertical"→ Select "Meio"

Now we can start adding visuals to this Tooltip page.

Let’s create a measurement to show the name of the selected product and add this measurement to a card.

ProdutoSelecionado =
SELECTEDVALUE ( dProduto[NomeProduto] )

At last insert a card with “Faturamento”, “Custo” and “ProdutoSelecionado”.

Figure 16: Tooltip page

You will need to return to the home page in visualizations that has the bar graph to bind this Tooltip to it, look:

Figure 17: Binding Tooltip to the Bar Chart

Just search for “Dica de Ferramenta”, select “Página de Relatório” in “Tipo” and then select the page name containing the Tooltip you want to show.

By scrolling the mouse over each of the bars, you will be able to see the Tooltip:

Figure 18: Result

To make it even cooler, you can add an image to Tooltip. Remember that the “dProduto” table has a column with the URL? Nothing is by chance!

To do this you will need to import a custom visual (free) called Simple Image. Just go to these dots and click on “Obter mais visuais”. In the next window you search for “Simple Image” and add it.

Figure 19: Importing Simple Image (free custom visual)

Before adding the URL column to the view, make sure it is categorized as “URL da Imagem”. If not, adjust it this way:

Figure 20: Categorizing URL column

See how it looks:

Figure 21: Using Simple Image

Done! Now see how spectacular the Tooltip result with the image:

Figure 22: Tooltip result

Drill-through

This tongue twister serves to show on another page details about a point in your report. It is different from Tooltip because this detail can only be seen on another page and you need to click some button to reach it.

Let’s create our detail page!

Create a new page and rename it to “Detalhe das Vendas”. Hide it (like we did with the Tooltip page). Add a table with the following fields:

Figure 23: Sales Detail Table

We will also add the card with the name of the selected product and its image (similar to what we did with Tooltip), so if you prefer, you can even copy those two looks.

After that, you need to activate Drill-through by dragging the “NomeProduto” column into that blank space:

Figure 24: Activating Drill-through

By doing this you will make it possible for the user to reach this page through any graphic that has the “NomeProduto” column. Note that by doing this, a back button has appeared on the top of the page

You may be wondering how the user will get to this page since there are no callsigns for this on the General page. The user will need to right-click on the item to get to Drill-Through. Look how it works:

Figure 25: Detailing sales of a product (right-click)

Many people complain about this feature because it is not intuitive for the user to do it, especially if he has no experience with Power BI reporting. So, we will create a button to help!

Go to the tool bar in “Inserir”“Botões” → “Em branco”.

Figure 26: Creating a button to Drill-Through

Note that Drill-Through only works if you select a point on the graph. So let’s make the button presents a text if you are without the selection of that point and another text when you have the selection. It looks complex but it’s not!

Edit the “Texto do Botão” and select “Desabilitado” in the Status dropdown of the button with “Selecione uma fruta no visual acima”.

Figure 27: Button text for disable status

Now, create a new measuring to show the selection that was made by the user:

TextoBotao =
“Detalhar Vendas de “
    SELECTEDVALUE ( dProduto[NomeProduto] )

See, See, the DAX formula above is quite simple! We only use the SELECTEDVALUE function to capture the name of the selected product (in the bar graph).

The ace in the hole will be add this measure to “Status Padrão”, look:

Figure 28: Button text using measure

Finally, let’s add the command to be executed when the button is clicked. Search for “Ação” in the button settings and activate it. Then select Drill-Through and “Detalhe de Vendas”:

Figure 29: Setting the button action

During Live #34, a participant asked how she could do so that the filters of other graphics were not considered when doing Drill-Through. Just uncheck this item:

Figure 30: Changing Drill-Through Filter Configuration

During Live#34 I did some really cool dashboards in which I used Tooltips, Drill Down and Drill-Through in many ways and if you are curious you can take a look here.

Well, thanks for getting here. I hope you enjoyed it!
If you have any questions or suggestions for next lives leave your comment here below.


See you!

Cheers,
Leonardo.

Share this post:
Share on facebook
Share on linkedin
Share on twitter
Share on pinterest