Power BI: Using Drilldown in Narratives


Documentation for Arria NLG’s Add-in for Power BI Desktop


This documentation is organized as follows:

Overview of drilldown narratives

Drilldown — an important and oft-used feature in BI dashboards — allows users to navigate to a different layer of data granularity by clicking a control on a visualization. When a dimension of a dataset contains a hierarchy, you can drill down to reveal additional details. This lets you view aggregated, summary data and then hierarchically explore deeper levels of the data for more specific analysis.

The drilldown feature of Arria’s add-in for Power BI offers narratives at different levels of data granularity. For example, you have a sales dataset with a location as a dimension. You want to look at sales by a hierarchy made up of country, state, and city. Initially, you want to describe sales performance by country and let users drill down into the state and city levels as they wish. The narrative changes its reporting granularity to follow along with the users’ drilldowns.

The examples below show a narrative that describes drivers of expense variance at an aggregated level (Account Level 2) alongside a bar chart with drilldown.

After drilling down, the example below shows the narrative describing drivers of expense variance at a more detailed level (Account Level 4) and the corresponding chart with its drilled-into detailed level.

Note: You can configure up to four drilldown levels within the add-in.

Configuring a drilldown narrative

This example for configuring a drilldown narrative uses a version of a Power BI project that looks at expenditures.

1. Download this Power BI project and open it in Power BI.

2. In NLG Studio, import this sample drilldown Studio project (the project is the JSON file inside the ZIP file).

3. In NLG Studio, publish the project, then:
a. Make note of the URL.
b. Generate the API key and make note of it.
Note: See Creating a custom narrative for details of how to publish a Studio project.

4. Configure the add-in with the URL and API key of the NLG Studio project.

5. Click Generate Text. You should see the narrative generated, but note that there is no drilldown control.

6. Now let’s configure the drilldown control. In this case, we will configure an account hierarchy using the fields in the Account table.

7. With the Arria add-in selected within the dashboard, click Format under the Visualizations pane.

8. Click Drill down filter 1 to expand this setting.

9. In the configuration for Drill down filter 1, enter the following:
a. In the Key field, enter accountLevel.
b. In the Label field, enter Acc Level.
c. In the Table name field, enter the table to be used (Account).

Once you have done the above configuration for the drilldown, the Arria add-in creates a drop-down with the selected columns of the configured table representing the account hierarchy.

Note: While you can now see the drilldown control element added to the widget, when you use it to make different selections, your selections will have no bearing on the narrative generated.

At run time, the selected value within the drop-down is passed to the API. The information is embedded in the BI data format as shown below:

"drillDown": {
"accountLevel": "Level 3"
},

This passed value needs to be used within the project in NLG Studio. Let’s go back to the project in Studio.

10. In Power BI, in the Arria widget, click the cog.
11. Click the Open with Studio button to go back to your Studio project.

Modifying the Studio project for the drilldown

Now you can access the value in the narrative script as shown below and assign that to a variable:

accountLevel=WholeJSON['dataset']['0']['drillDown']['accountLevel'];

1. In NLG Studio, look at the Main script.

2. In the Compose view, in the Main script, change the first line:

accountLevel='Level 3'
to
accountLevel=WholeJSON['dataset']['0']['drillDowns'][0]['accountLevel']

3. Publish the project again.

With this edit you have made, the accountLevel variable in your narrative script will now be used to access the dashboard user’s selected level with the account hierarchy.

4. In Power BI, try selecting different drilldown levels to see how the narrative changes.

Other documentation for Arria’s add-in to Power BI

For more information, see: