Category: Power BI

Your Power BI report needs to be as user friendly as possible for an end user to make insightful business decisions efficiently. So, what better way to grab their attention with the most salient sections of your report than to use Bookmark Navigator buttons. These buttons usually appear on the opening page and work by directing the end user to another page that is automatically filtered to the specific metrics that they want to see. A clearly labelled button is much easier to use than the filter tools so is ideal for those who have limited knowledge of Power BI.

What is a Bookmark, and why are they useful

 

A bookmark in a Power BI report is simply a snapshot of a filtered view captured in the report’s memory. Just like a bookmark on a website, it reflects the current view and can be ‘revisited’ any time you like. A Power BI report may contain many pages with many visuals on each page. It can be somewhat overwhelming to navigate to the data of interest so bookmarks in the form of buttons can help with this analysis and help you be more productive

How to get started

 

There are 2 stages to creating Bookmark Buttons. First you need to create the bookmark on one page and then attach it to a button on another page. Up until November 2021, this was the only way to create these buttons. Since then the new Navigator Bookmark button feature has made the ‘button attachment stage’ a lot more automated.

Let’s say you are reporting on the number of orders for each Product. You want to select a particular Product Category so that the report instantly directs you to a filtered list of all products’ orders for this category e.g. Tennis equipment (see below)

 

1. In Power BI Desktop, create a ‘Welcome Page’ (for the Bookmark buttons)
2. On a new page create a Table based on Products and the Number of Orders
3. Next, create a Treemap visual based on ‘Quantity by Category’
4. Go to VIEW > BOOKMARKS to display a Bookmark pane:

5. Now select ‘Tennis Equipment’ for example, from the Treemap to show all tennis related products
6. Click the ‘Add’ button, go to the 3 dots, and choose RENAME from the list. Type ‘Tennis’ to label the bookmark
7. Repeat steps 5 and 6 for two more categories
8. Create a 4^th Bookmark based on ‘All Categories’ i.e. one which shows no filtering
9. Now click on each bookmark to check it reflects the correct filtered state
10. On the ‘Welcome page’ go to INSERT > BUTTONS > NAVIGATOR > BOOKMARK NAVIGATOR

This action automatically displays a horizontal set of buttons:

 

11. Holding down the ‘Control’ key, click on one of the buttons and you will be directed to the filtered view of the other page

Once your report is published, there is no need to use the Control key to activate the Bookmark buttons.

Conclusion

Using Bookmark Navigator buttons within your Power BI reports can significantly improve the end user experience. This will inevitably help them to be more efficient and profitable when making those all-important business decisions.

 

To learn more about Digital Storytelling, click on the link below:

https://www.stl-training.co.uk/sharing/principles-of-data-storytelling/83

If you want to add dynamic text to give your audience more detailed Power BI reports, you can follow STL’s LinkedIn page. This is first part of the series, but more will follow.

In another series of blog posts from STL about AI visuals, you could see how the smart narrative visual could analyse another visual and generate a text box with useful text (see link below).

https://www.stl-training.co.uk/b/power-bi-ai-visuals-part-4-smart-narrative/

The smart narrative tool looks at trends and key influencers, but if you want to be in charge of the text you want to display on a report, you will have to write the DAX to get it.

In the example below a line chart displays sales profit by customer segment and the chart is filtered by a slicer to display customers in U.S.A.

Under the chart a text box is added, and by DAX the text box displays useful information for the report audience.

The text in the text box is dynamic and will change by all filtering on this page.

Below, Canada is selected from the slicer. You can see that the dynamic text now has changed to show Canada details.

A year slicer has been added to the report (The interaction between the year slicer and the line chart has been deactivated), and the text box’s information have been changed again to only display the selected year.

And now to the DAX part.

 

It may look a bit advanced.

A measure has been created see following DAX:

Country text =

” The line chart above shows Profit for ” & VALUES ( Customers[Country] ) & ” over years by customer segment. The Sales growth for ”

& VALUES ( Customers[Country] ) & ” is ”

& FORMAT ( [Growth], “0.00” ) & “%, and the bestselling product in ”

& VALUES ( Customers[Country] ) & ” is ”

& CONCATENATEX (

TOPN ( 1, VALUES ( Items[Product Description] ), [Sales] ),

[Product Description],

“;”

) & “. At the moment we have ”

& COUNT ( Customers[Customer Id] ) & ” customers in ”

& VALUES ( Customers[Country] ) & ” and the average order sales is ”

& FORMAT ( DIVIDE ( [Sales], COUNTROWS ( Orders ), 0 ), “currency”, “en-gb” ) & “, and average quantity per order is ”

& FORMAT (

DIVIDE ( SUM ( Line_Items[Quantity] ), COUNTROWS ( orders ), 0 ),

“0,00”

) & ” units.”

 

In the DAX you will find a combination of other DAX measures, text, and DAX functions all concatenated by & (ampersand). And all free text surrounded by “” (quotation marks).

To make it simpler I will break down the DAX in smaller pieces.

 

Part 1:

Country text =

” The line chart above shows Profit for ” & VALUES ( Customers[Country] ) & ” over years by customer segment. The Sales growth for ”

& VALUES ( Customers[Country] ) & ” is ”

 

 

The VALUES ( Customers[Country] ) return the value selected from the slicer, so if Canada has been selected from the slicer, the first part will return

“The line chart above shows Profit for Canada over years by customer segment. The Sales growth for Canada is”

 

Part 2:

& FORMAT ( [Growth], “0.00” ) & “%, and the bestselling product in ”

& VALUES ( Customers[Country] ) & ” is ”

 

In my example model I have used a time intelligence DAX function SAMEPERIODLASTYEAR to be able to calculate the growth.

Last year = CALCULATE([Sales],SAMEPERIODLASTYEAR(Dates[Dates]))

 

(If you want more insight about time intelligence DAX function, follow the link below:

 

https://www.stl-training.co.uk/b/power-bi-mysterious-calculate-function-3-time-intelligence/)

Then the Growth measure looks like this:

Growth = divide([Sales]-[Last year],[Last year],0)

To get it formatted you can use the Format function. In this example with two decimals and the % sign.

Part 2 if Canada is selected from the slicer will return:

“3.88%, and the bestselling product in Canada is”

 

Part 3:

& CONCATENATEX (

TOPN ( 1, VALUES ( Items[Product Description] ), [Sales] ),

[Product Description],

“;”

) & “. At the moment we have ”

 

The CONCATENATEX function is used to extract the product name. The TOPN function is used to find the top 1 best selling product by the sales.

Part 3 if Canada is selected from the slicer will return:

“cross – country ski. AT the moment we have”

(cross = country ski is number 1 selling product in Canada in the data set used here)

 

Part 4:

& COUNT ( Customers[Customer Id] ) & ” customers in ”

& VALUES ( Customers[Country] ) & ” and the average order sales is ”

& FORMAT ( DIVIDE ( [Sales], COUNTROWS ( Orders ), 0 ), “currency”, “en-gb” ) & “, and average quantity per order is ”

 

The number of customers in Canada can be displayed in the text string by count the number of customer IDs.

And to return the average sales per order can be done by dividing the sales by number of orders. In the example data set a table store a record for each order in an Orders table.

The FORMAT function is here used to return currency formatting.

Part 4 if Canada is selected from the slicer will return:

“8 customers in Canada and the average sales order is £23,854.62, and average quantity per order is ”

 

Part 5:

& FORMAT (

DIVIDE ( SUM ( Line_Items[Quantity] ), COUNTROWS ( orders ), 0 ),

“0,00”

) & ” units.”

 

Again, the FORMAT function is used, this time to get 0 decimals, and the DIVIDE function to get the average quantity per order.

Part 5 if Canada is selected from the slicer will return:

“489 units.”

 

The whole text string if Canada is selected from the slicer will return:

“The line chart above shows Profit for Canada over years by customer segment. The Sales growth for Canada is 3.88%, and the bestselling product in Canada is cross – country ski. At the moment we have 8 customers in Canada and the average order sales is £23,854.62, and average quantity per order is 489 units.”

Last step is to add the dynamic text measure to a text box.

Add a Text box to the page and turn on Title in the Format pane to the right.

Next to the Text input box press the fx button.

Click the drop-down arrow next to the “What field should we base this on?” navigate, find the measure, select it, and click OK bottom right.

Conclusion

You can add much more information to your reports by creating dynamic text boxes. They allow you to be able to explain the visuals, you can point out specific details, which are important for the audience.

You can find more examples like this by following STL’s LinkedIn page. Dynamic text can be used for much more than just creating text for text boxes.