Analyze data with Power BI
Microsoft ‘s business analytics product,
Power BI, delivers interactive data visualization BI capabilities that allow
users to see and share data and insights throughout their organisation. Power
BI provides insight data by using data interactively and exploring it by
visualizations. Create visualizations and reports using the data models.
This module goes beyond viewing and exploring your
content and explains how to interact with it by working with reports and
dashboards to uncover and share new business insights.
Learning objectives
In this module, you will:
- Explore how filters
are applied to your data
- Learn about
different ways that you can filter reports
- Create personal
bookmarks
- Analyze and export
data
Filter data with Power BI
Data is the core of Power BI. As you explore reports,
each visual draws its underlying data from sources that often contain far more
data than you need. Power BI offers several ways to filter and highlight
reports. Knowing how to filter data is the key to finding the right
information.
Slicers
A simple type of filtering that you can use directly
on the report page is called a slicer. Slicers provide clues to
ways you can filter the results in the visuals on a report page. There are
several different types of slicers: numeric, categorical, and date. Slicers
make it easy to filter all the visuals on the page at once.
If you want to select more than one field, hold the
Ctrl key and click additional fields.
Explore the Filters pane
Another way to filter data is by opening and modifying
filters in the Filters pane. The Filters pane contains filters that were added
to the report by the report designer. As a consumer, you can interact with the
filters and save your changes but can't add new filters.
The four types of filters are:
- Report – Applies to all pages in the report.
- Page – Applies to all the visuals on the current
report page.
- Visual – Applies to a single visual on a report
page. You only see visual level filters if you've selected a visual on the
report canvas.
- Drillthrough – Allows you to explore successively more
detailed views within a single visual.
For example, in the following screenshot, the designer
added three page-level filters: Segment, Year,
and Region. Notice that Year is currently set to
filter to the year 2014.
You can apply filters on the Filters pane,
or you can select data directly in the report to narrow by date, category,
geography, and so on.
Note
You can explore the data by modifying the existing
filters. The changes you make are saved for you (not others) with the report,
including when you open the report in a mobile app.
When you exit the report, your filters are saved. To
undo your filtering and return to the default filtering, slicing, drilling, and
sorting that the report author set, select Reset to default from
the top menu bar.
Clear a filter
In either advanced or basic filtering mode, select the
eraser icon to clear the filter. As you adjust the filter, the search query
updates to reflect your choices.
Advanced mode
Selecting check boxes to apply simple filters is
effective, but sometimes you need to apply a filter based on a dynamic range.
You might want to filter between two dates, for example, from May 1, 2018 to
July 31, 2018. You could filter within a certain number value, for example,
sales between 10,000𝑎𝑛𝑑50,000. In these situations, you need advanced mode.
If the values are continuous (not discrete) or
represent a range, you can select the field name to open the advanced filter
mode. Use the drop-down menu and text boxes to specify a range of values that
you want to see.
If the field values represent date or time, you can
specify a start and end time when using the Date and Time filters.
Change how a chart is sorted in a report
In a Power BI report, you can sort most visuals
alphabetically or by numeric values of each category. For example, the
following chart is sorted by the category Store name.
Changing the sort from a category (store name) to a
value (sales per square feet) is simple. In this example, you would select the
ellipsis (...) and choose Sort by > Sales Per Sq Ft. Optionally,
you might select the ellipsis again and choose Sort Descending.
Note
Not all visuals can be sorted. For example, the
following visuals cannot be sorted: Treemap, Map, Filled Map, Scatter, Gauge,
Card, Multi Row Card, and Waterfall.
Save changes you make to the sort order
Power BI reports keep the filters, slicers, sorting,
and other data view changes that you make. When you leave a report and then
return later, your changes are saved. If you want to revert your changes to the
report designer's settings, select Reset to default from the
top menu bar.
Drill down in a visual
When a visual has a hierarchy, you can drill down to
reveal additional details. For example, you might have a visual that looks at
Olympic medal count by a hierarchy that is made up of sport, discipline, and
event.
In this example, you might select the Aquatics element
to see data for swimming, diving, and water polo. Select the Diving element
to see details for springboard, platform, and synchronized diving events.
If you're unsure of which Power BI visuals contain a
hierarchy, hover over a visual. If you see the following drill controls in the
top corner, your visual has a hierarchy.
Dates are a unique type of hierarchy. When the report
designer adds a date field to a visual, Power BI automatically adds a time
hierarchy that contains year, quarter, month, and day.
Use bookmarks to share insights and build stories
You can interact with filters and save the changes for
yourself only. However, sometimes you might want to share certain filtered
views with your team. In those cases, you can create report bookmarks.
Using bookmarks in Power BI helps you capture the
currently configured view of a report page, including filtering and the state
of visuals. You can return later to that state by selecting the saved bookmark.
To see report bookmarks in action, go to any Power BI
report to which you have view or edit access and select the Bookmarks drop-down
menu on the action bar.
Create bookmarks
When you've selected the report and view that you
like, expand the Bookmarks drop-down menu and select Add
personal bookmark. By default, Power BI will suggest a generic name for
your bookmark. You can type in your own name and select Save.
After creating a bookmark, you can display it by
selecting the bookmark name in the drop-down list. Notice that the breadcrumb
for the report will now contain the bookmark that you're currently viewing.
Note
You will be able to create a maximum of 20 bookmarks
per report.
Open and view bookmarks
The report designer will sometimes include bookmarks
as part of the report. To view either your personal bookmark or a report
bookmark, select the bookmark icon and make your selection. Bookmark icons are
listed on the Bookmark pane that you can open by selecting Bookmarks
> Show more bookmarks.
View your data
Visuals are created by report and dashboard designers
and then shared with consumers. As a consumer, you can choose to show, export,
and analyze the data that was used to create each visual.
Show data
Power BI visuals are created from data that you can
view. When you turn on Show data, Power BI displays the data below
the visual.
In Power BI, you can open a report and select a
visual. To display the data that was used to create the visual, select the
visual's More options (...) and select Show as a table.
Export data to Excel
Occasionally, you might want to use Excel to view and
interact with Power BI data. With the Analyze in Excel feature,
you can do just that. This option also allows you to access PivotTable, chart,
and slicer features in Excel based on the semantic model that exists in Power
BI.
When you select Analyze in Excel, Power BI creates an
Office Data Connection (.ODC) file and downloads it from the browser to your
computer.
Note
Your exported data in Excel is your own copy of the
data and will not be updated automatically if the original data is updated.
Analyze data in Excel
When you open the file in Excel, an empty PivotTable and Fields list
appears with the tables, fields, and measures from the Power BI semantic model.
You can create PivotTables and charts, and analyze that semantic model just as
you would with a local semantic model in Excel.
Note
If you'd like to see the data that is used to create a
visual, you can export that data to Excel as an .xlsx or .csv file.
Ask questions of your data
Sometimes, the fastest way to get answers about your
data is by asking questions in the Q&A feature of Power BI.
Note
Though Power BI Q&A only currently supports
answering queries that are asked in English, a preview is available for Spanish
that can be enabled by your Power BI administrator.
Explore Q&A
You can use Q&A to explore your data by using the
intuitive, natural language capabilities of Power BI and receive answers in the
form of charts and graphs.
Ask a question
Ask a question about your data in Q&A by using
natural language. Natural language refers to the ordinary language that humans
use to communicate with one another every day. An example would be, “What are
the total units by region?”
Q&A is available on dashboards and reports in
Power BI. To open the Q&A screen, go to the dashboard and place your cursor
in the question box.
If the visuals' axis labels and values include the
words sales, account, month, and opportunities,
then you can confidently ask questions that use those values. For example,
"Which account has the highest opportunity" or
"Show sales by month as a bar chart."
Other helpful items are provided on the side of the
screen. For each semantic model, Q&A shows you keywords and occasionally
shows you some sample or suggested questions. Select any item to add them to
the question box.
Another way that Q&A helps you ask questions is
with prompts, autocomplete, and visual cues.
Q&A visuals
Q&A picks the best visual based on the data being
displayed. For example, numbers may be displayed as a line chart while cities
are more likely to be displayed as a map.
You can also tell Q&A which visual to use by
adding it to your question. Q&A will prompt you with a list of workable
visual types. Using the previous example, you could ask, "What are the
total units by region by pie chart?"
Words and terminology that Q&A
recognizes
The best way to determine if Power BI recognizes a
keyword is by typing it in the question box. If the word or term appears
dimmed, then Power BI doesn't recognize it. The keywords in the following table
use singular and present tense, but all tenses and pluralities are recognized.
For more detailed information, see: Tips for
asking questions in Power BI Q&A
Expand table
|
Category |
Keywords |
|
Aggregates |
total, sum, amount, number, quantity, count,
average, most, least, fewest, largest, smallest, highest, biggest, maximum,
max, greatest, lowest, littlest, minimum, min |
|
Comparisons |
vs, versus, compared to, compared with |
|
Dates |
Power BI recognizes most date terms (day, week,
month, year, quarter, decade, and so on) |
|
Relative dates |
today, right now, current time, yesterday, tomorrow,
the current, next, the coming, last, previous, ago, before now, sooner than,
after, later than, from, at, on, from now, after now, in the future, past,
last, previous, within, in, over, N days ago, N days from now, next, once,
twice. Example: Count of orders in the past 6 days. |
|
Query commands |
sorted, sort by, direction, group, group by, by,
show, list, display, give me, name, just, only, arrange, rank, compare, to,
with, against, alphabetically, ascending, descending, order |
|
Range |
greater, more, larger, above, over, >, less,
smaller, fewer, below, under, <, at least, no less than, >=, at most,
no more than, <=, in, between, in the range of, from, later, earlier,
sooner, after, on, at, later than, after, since, starting with, starting
from, ending with |
|
Times |
AM, PM, o'clock, noon, midnight, hour, minute,
second, hh:mm:ss. Examples: 10 PM, 10:35 PM, 10:35:15 pm, 10 o'clock, noon,
midnight, hour, minute, second. |
Q&A helps phrase the question
Q&A does its best to understand and answer the
question by:
- Autocompleting words
and questions. It uses various strategies, including autocompleting
recognized words and previously used questions that returned valid
responses. If more than one autocomplete option is available, they're
presented in a drop-down list.
- Correcting spelling.
- Providing a preview
of the answer in the form of a visual. The visual updates as you type and
edit the question (it doesn't wait for you to press Enter).
- Suggesting
replacement terms from the underlying semantic model(s) when you move the
cursor back in the question box.
- Restating the
question based on the data in the underlying semantic model(s). Q&A
replaces the words you used with synonyms from the underlying semantic
model(s). By reading the restatement, you know whether Q&A understood
your question or not.
- Dimming words it doesn't understand.
pply filters to reports
In this unit, you review a Sales and Marketing sample
report with an analytical mindset. For this scenario, you're the new sales
manager at a clothing manufacturing company named Van Arsdel. Your
first assignment is to analyze your sales and marketing data to find why
there's an unexpected dip in June sales, and what are the best opportunities.
Within a Sales and Marketing sample app,
you start on the YTD Category report page by selecting
the YTD Category tab at the left of the screen.
Notice the anomaly in the Total Units by Month
and Manufacturer chart.
Use the Filters pane to see all of
the filters that the report designer applied to that visual.
The report designer filtered for Van Arsdel and our
top three competitors and for the year 2014. The report is also filtered for
the Central region, and you want to see all of the regions. You can
clear the Region filter by clicking the eraser icon on that
filter.
Mouse over the Total Units YTD by Manufacturer
and Region and note Van Arsdel competes in Moderation and Convenience.
Hover over our competitors to see segments in which
they compete. Do you see any patterns?
Explore the same question just for the month of June.
To cross-filter this report page for June, in the Total Units by Month
and Manufacturer visual, click on the label June-14.
Notice that all of the visuals in this report page update for June.
Notice the cross-highlighting of the Total
Units YTD by Manufacturer and Region visual.
Hover over each company to see the pattern of segments
during June.
What can you conclude about the two most successful
segments in June and why are our total sales dipping during that time?
To explore the same question in a different way: go to
the Growth Opportunities report by selecting the Growth
Opportunities tab.
Hover over the Total Units by Segment bar
chart visual and then select the "Drill Up" icon (the up arrow) to
see "Total Units by Manufacturer".
Clear the isVanArsdel and the Segment filters
by clicking the eraser icons. This allows us to see all of the companies and
segments. Notice that Van Arsdel is the leading manufacturer overall.
Cross-filter for June on the Total Units and
Total Units by R12Ms by Month visual by selecting June-14.
Notice once again how Van Arsdel did in June.
Drill down to view segments in June by clicking the
"Click to turn on Drill Down" icon and then the "Go to the next
level in the hierarchy" icon.
Once again, what segments are the most successful in
June.
Why are Van Arsdel sales failing in June? What could
we do to better compete?
No comments:
Post a Comment