Sonoma Partners Microsoft CRM and Salesforce Blog

Using Q & A to Create Reports in Power BI

Today's blog post was written by Brendan Landers, VP of Consulting at Sonoma Partners.

Power BI Q&A enables users to ask natural language questions and get answers in the form of visuals or reports automatically created with the data that best answers their question.  Q & A has been available in Power BI for some time now, but it was only available for datasets with visualizations that had been pinned to dashboards in the Power BI Service.  The end user could leverage Q & A within those dashboards, but someone had to design the dataset and visualizations with Q & A in mind for it to be useful.  This video published by the Power BI team showcases how the functionality works through Dashboards.

With the December release of Power BI, you can now use Q & A to create your reports.

In this blog post, I will show you how to do so in the latest version of the Power BI Desktop.

To illustrate, I have a simple excel file with four tabs which I am using as the data set.  The tabs are:

  • Customer – a sample customer list
  • Opportunities – a sample list of opportunities
  • Orders – a sample list of orders
  • Users – a sample list of AEs

We could easily connect this to Dynamics or Salesforce to get those objects for your data set, but I am using excel for the sake of simplicity.  Additionally, I have setup simple relationships between the objects using the autodetect feature. 

Brelanders 1

Once I have my data setup, I can start using Q & A to create reports. 

*Please note Q&A for creation is a Preview feature, and you may need to enable it through options (Click on Start > Options and Settings > Options > Preview Features).

Next, to use the new feature you will first click the Ask a Question button in the ribbon. 

Brelanders 3

Once you do so, you will see a blank visualization appear with the Q & A box available for input.

Brelanders 4

Now we are ready to build a chart.  First, let’s look at the Weighted Revenue by Year for each of our AEs.  The fields I need in my data set for this report are the following:

  • Weighted Revenue (from Opportunity)
  • Expected Close Date (from Opportunity)
  • AE (from User)

In the Q & A input box, I will start typing.  I first start with Weighted Revenue.  As soon as I begin typing you will notice intellisense auto-showing the fields.  In this case, by just typing the letter "W" I can see the field I need.

Brelanders 5

I select Weighted Revenue, and Power BI provides a Card visualization with the sum of weighted revenue in my Opportunity table.  Pretty cool.  Next, I want to see the data by AE.  To do so, I type the word "by," the field I need, and voila!  After that selection, Power BI shows me a bar chart of Weighted Revenue by AE.  Even cooler.

Brelanders 6

While that is interesting, I’d like to take it a step further to make this more meaningful.  To do so, I am going to add Expected Close Date into the mix to see Weighted Revenue by AE by Year.  I’m guessing at this point you get how I might do so, but for those who jumped to the end, I will add the words "By Expected Close Date" to my Q&A.  As I type in Expected Close Date, I notice that Power BI starts to show off a bit and, since it’s a date field, it serves me some options from a date hierarchy. 

Brelanders 7

I am going to select the Expected Close Year option which will show the data by rep by year.

Brelanders 8

And we are done!  In literally seconds any user can create meaningful charts with simply typing what they want.  The coolest.

If you have questions about this post or anything Power BI related, feel free to reach out.

Topics: Analytics Microsoft Dynamics 365