Power BI – Dynamic Text for More Detailed Reports

Visualising data is crucial for decision making in any industry.

Power BI reports are a brilliantly efficient way to achieve this, as users have several useful visual options.

In this blog post you will learn how Power BI can display dynamic text for more detailed reports.

Power BI Dynamic Text

We will be using examples of sales data and HR data, but the logic, methods, and techniques can be used across all different industries, such as finance, healthcare, and retail and across all types of data sets.


To get the full benefit from this blog post, some experience and knowledge about DAX (Data Analysis eXpressions) is needed. We will use some DAX functions, but you could use a huge number of different DAX functions to create Dynamic text for more detailed reports.

Power BI Report Visuals

You have different options to provide your audience explanations or additional information to your Power BI report’s visuals. You can let Power BI desktop search for key influencers in your data model based on the visual by using the Artificial Intelligence Visual Smart Narrative.

Power BI Dynamic Text

You can write DAX measures and be in fully control of the text displayed in a text box (see the example below and the DAX used to achieve the example text).

Power BI Dynamic Text

Dyn 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 best selling product in ”

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


TOPN ( 1,  values(Items[Product Description]), [Sales],DESC ),

[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 ”


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


) & ” units.”


The DAX in this example will take to account any filtering on the report page and change the dynamic text based on the filters.

You can use a combination of the two options, and you can also use the AI tool Q&A to generate your dynamic text boxes. Furthermore, you can create Custom Tooltips with dynamic text which will popup when your audience hover over visuals.

In Power BI desktop all dynamic text will be underlined (a blue line) and cannot be changed, but when the report has been published and shared, the audience will not see the blue lines.

Example 1 – Combination of Smart Narrative and your own DAX measures and text.

A line chart visual displaying sales over years and months has been selected and to get Power BI desktop to generate the text you will have to click “Narrative” on the Insert tab in the AI Visuals group.

You will get a text box where Power BI will write AI text based on the data set and selected visual.

Power BI Dynamic Text

You cannot control the AI text the Narrative tool writes, but to add your own text, just click inside the text box and type. The text “The total sales for all months” is entered here. After the added text, the total sales amount is going to be displayed.

Power BI Dynamic Text

When you click inside the text box a tool bar will appear. Click +VALUE and you can type in the DAX measure you want to display the result from. Here I want to display the result from a DAX measure called sales. I have formatted the result as currency, and I named the result “totsales.” The name can be used to display the result somewhere else in the text box.

Power BI Dynamic Text

In the same text box, you may want to explain to the audience, how the visual and the information have been filtered. In this example a slicer has been added to page to filter the data by country.

The following DAX measure will pick up if the column with the heading Country in the table “tblsales” is filtered. If it is, it will return the country name selected from the slicer. Otherwise, it will return the text string ” all countries”.

Filtered countries = if(ISFILTERED(tblsales[Country]),SELECTEDVALUE(tblsales[Country]),”all countries”)

Power BI Dynamic Text

If you look at the screenshot above, you can see that the total sales amount is added to the text box and a new line at the top has been typed:
“This page visuals and the information in this text box display data from”

Place the mouse course where you want to add the dynamic text and click “+Value” and again the options will appear.

Power BI Dynamic Text

Now the text will show this if the page has not been filtered by country:

Power BI Dynamic Text

And this if the page has been filtered by “UK”:

Power BI Dynamic Text

Example 2 – Combination of Q&A artificial intelligence and your own DAX measures and text.

If you do not want the smart narrative dynamic text, you can use the same method as in example 1 above. You will then just need to add a text box to the page.

In this example you will also see how the artificial intelligence tool Q&A can benefit you when you are creating dynamic text.

If we type “The percentage sales margin for” in a text box, then as in example 1 the DAX measure Filtered countries is used to return the country selected from the slicer. Then “is” is typed and then as in example 1, where a measure called Sales was added, another measure is added where the percentage sales margin was calculated. The same has been done to get the result from a profit measure.

At this point, the text box displays:

The percentage sales margin for Canada is 49.19 %,

the profit for Canada is £269,079,481,

and the total quantity for Canada is


You will not even always have to do measures to return calculated results in dynamic text. Here the artificial intelligence tool Q&A can be particularly useful.

To get the total quantity in the text box click “+Value.”

Power BI Dynamic Text

This will look in your data set and find a column with the name quantity and sum all the values in the column.

You will need to know your column headers.

Power BI Dynamic Text

“top 1 ClientName profit” Q&A will look in the column ClientName and find top 1 client’s value based on a measure called profit.

And the result will look like this:

Power BI Dynamic Text

And this:

Power BI Dynamic Text

Will return the name of top 1 client based on profit.

Power BI Dynamic Text

Example 3 – Dynamic text in custom Tooltips for more detailed reports.

The methods are the same as above! The only difference is that the text box needs to be created on a tooltip page.

Power BI Dynamic Text

The dynamic text is also filtered by the data point you hover over on the visual. In the screen shot above, the mouse courser is on top of the data point for August 2019.


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

Filtering can be confusing in a Power BI report for the audience, but as you saw in this blog post, you can explain filters in dynamic text.

Further Reading:


Excel Data Visualisation: A Powerful Decision Making Tool

Data visualisation is a powerful tool that allows you to view data more clearly. This is vitally important for businesses to help them with crucial decision making.

Excel Data Visualisation: A Powerful Decision Making Tool

What is data visualisation?

Data visualisation is the process of turning raw data into graphical representations. It works by taking selected data in Excel and then choosing from a variety of different charts the one that is best suited to the data. These charts can make it easy to communicate trends in data and draw conclusions.

How can data visualisation help businesses?

Data visualisation can help businesses make better decisions through identifying key trends, patterns, and outliers in their data. In turn, it can help them communicate insights to stakeholders more effectively.


Charts in Excel can be created in two different ways depending on the complexity of the data. The first method is to make a chart from a simple dataset and is always a good place to start if you have this data already available.

The second method is to convert a more detailed transactional dataset (e.g. daily sales transactions) into a PivotChart (a visual equivalent of a Pivot Table). This method is ideal for converting ‘raw’ data into summary reports and provides more scope for greater insights.

On the other hand, the first method is more suited to data that has already been summarised but is more limited in showing insights. This blog will show both approaches starting with the first method as it is the simpler of the two to master.

Creating a Chart (first method)

Let’s say an Area Manager is tasked with comparing the sales performance of Sales Reps in terms of quantities of products sold. The Excel table below contains the summary totals for each Rep:

Excel Data Visualisation: A Powerful Decision Making Tool


1. Provided there are no blank rows or columns

within the dataset, you can just select any cell

in the data and go to INSERT > PIE CHART ICON

Excel Data Visualisation: A Powerful Decision Making Tool

2. Select the 2-D Pie icon

3. The Pie Chart will now appear – see below:

Excel Data Visualisation: A Powerful Decision Making Tool

Editing a Chart

The ‘Chart Title’ and ‘Legend’ (Colour key for a category list) are 2 of the 3 ‘Chart Elements’ that go to make up a Pie Chart. The 3rd element is ‘Data Labels’ which can be displayed using the ‘Chart Elements’ icon: Excel Data Visualisation: A Powerful Decision Making Tool

  1. Click on the icon and tick the box for ‘Data Labels’

Excel Data Visualisation: A Powerful Decision Making Tool

  1. With the ‘Chart Title’ box ticked, select the text ‘Quantity’ and type ‘Total Product Quantity by Sales Rep.’ This is a ‘free’ text box that allows you to enter your own customised text
  2. With the ‘Legend’ selected, hover to the right and you will see a > icon. Select ‘Right’ to move the legend to the right-hand side of the ChartExcel Data Visualisation: A Powerful Decision Making Tool
  3. To add percentages to the Chart, go to the ‘Chart Elements’ icon and then DATA Labels – More Options.Excel Data Visualisation: A Powerful Decision Making Tool
  4. Note a list of tick boxes to display different ‘Label Options’. Tick ‘Percentage’ and untick ‘Value’ in the list

The result of all these changes can be seen below:

Excel Data Visualisation: A Powerful Decision Making Tool

Creating a PivotChart (second method)

The same Area Manager now wants to see more details in the Sales Reps’ performance e.g. Sales broken down by Individual Rep and Product. As the original dataset used in the first method only contains quantities by Rep, the Area Manager will need another more detailed set of data – a section of the data is shown below:

Excel Data Visualisation: A Powerful Decision Making Tool

Ensure the data has headings for each column and there are no blank rows or columns within the data itself.

    1. Select the PivotChart and go to PIVOTCHART ANALYSE > INSERT SLICER and tick for Product Description Select any cell within the data and go to INSERT > PIVOTCHART > PIVOTCHART (sub-list) The following dialog box will appear:Excel Data Visualisation: A Powerful Decision Making Tool
      Ensure all data is referenced in the ‘Table/Range’ field and then click OK. This action creates a new sheet with 3 objects: PivotChart, Pivot Table and PivotChart Fields pane:Excel Data Visualisation: A Powerful Decision Making Tool
      Think of the PivotChart Fields pane as the ‘control panel’ used to build the PivotChart and PivotTable
    2. In the Field List, tick the fields for Rep_LastName and Quantity to produce the following visuals:
      Excel Data Visualisation: A Powerful Decision Making Tool
      Notice that the default settings are to Sum the Quantities and create a ‘Column Chart.’
    3. To change it to a Pie Chart, select the chart and go to DESIGN > CHANGE CHART TYPE, select the Pie Chart option and click OK.
    4. Add Data labels and set them to Percentage as covered in the second method.
    5. To analyse sales broken down by Product, you can create a slicer (visual filter):
      Select the PivotChart and go to PIVOTCHART ANALYSE > INSERT                          SLICER and tick for Product Description
      Excel Data Visualisation: A Powerful Decision Making Tool
    6. Now click on an individual product (e.g. backpack) and notice how it interacts with the PivotChart and PivotTable:

Excel Data Visualisation: A Powerful Decision Making Tool

Industry sector examples

Examples of data visualisation from other industries could include:

      • HR Managers analysing number of sick days across departments
      • Data Analysts in healthcare checking patient attendance for different types of medical treatment
      • Events Coordinators tracking cost/revenue of events on a monthly basis
      • Customer Service Managers analysing call volume, call times and customer satisfaction scores generated from helpdesk data


Data visualisation is key to understanding your data better and can be applied using one of the methods described. If detailed data is available, the second method, “PivotChart” is likely to give you greater insights into your data. Many other industries (not just sales related) can benefit from this approach. So why not try out these methods on your own data and see how this extremely powerful tool can help you gain a competitive edge within your business.

Further Reading

Excel Or Power BI – Which is Better for Business Reporting?

7 Essential Excel Tricks Every Office Worker Needs to Know