CIO
15 power tips for Microsoft Power BI
By: Mary Brandscombe
Power BI is Microsoft’s interactive data visualization and analytics tool for business intelligence (BI). With Power BI, you can pull in data from a wide range of systems in the cloud and on premises and create dashboards that track the metrics you care about the most. You can even drill down into your data and (literally) ask questions about it.
Power BI’s rich reports or dashboards can be embedded in reporting portals you already use. And while its dashboards, reports, and visualizations can go far beyond bar and pie charts, you don’t need to be a designer to create them. You can also use Power BI to prepare and manage high-quality data to use across the business in other tools, including low-code apps. Here’s how to get more insights from the information you already have, in more areas than you might expect.
1. Visualize the services you use
Power BI has hundreds of content packs, templates, and integrations for hundreds of data services, apps, and services that include pre-set reports and visualizations. If you use Xero for accounting, or K2 Cloud to build business processes, or Adobe Marketing Cloud, SAP HANA, Salesforce, MailChimp, Marketo, or Google Analytics, you can use Power BI to visualize the data you have in those services, create reports against them, and bring them together in a custom dashboard. You can also set up the on-premises gateway to use Power BI to explore data sets on your own servers. That way you can compare website visitors with sales, or see which promotions have brought in new customers. You can create your own reports and visualizations, perform calculations (Power BI calls these calculated measures), and set access levels for individual users, data sources, or specific dashboards and reports to control who can view more sensitive information.
2. Tell stories with your data
Charts are great for numbers, but if you want to show information that changes over time in a way that’s easy to understand, Power BI’s new Timeline Storyteller is for you. With this tool, you can create a linear list of dates or times, or lay them out in circles, spirals, grids, or custom shapes. You can also show a chronological list, a sequence that shows the duration of events, or pick relative or logarithmic scales. Pick how to best represent, scale, and lay out your data, and Power BI will build a timeline from it. You can then use that to tell the history of your business, show how demand is growing, or explain anything else in which the sequence of events matters.
3. Explore ‘What-ifs’
You can compare scenarios in Excel, but Power BI lets you do it by dragging a slider bar to show changes. Add a calculated measure for a figure such as revenue and you can use the New Parameter button in Power BI Desktop to add parameters that change in your What-if scenario. That creates a calculated measure you can reference elsewhere; so if you create a What-if parameter for the number of customers who respond to a particular promotion you can plug that into a formula you create to show how many customer support tickets you can expect to have to deal with. Tick “Add slider to this page” in the What-if parameter dialog to add a slider bar that you can drag to show the difference when the number of customer responses is higher or lower.
4. Ask questions in your own words
You can also use the natural language features of Power BI to ask questions and get visualizations in response. Specify how the data should be presented — ask for “total sales by region by month as a line” — or let Power BI pick a layout that suits the data with a more general question like, “What were the sales numbers for last quarter?”
If there are tiles pinned to the dashboard, Q&A will suggest those as questions, and as you type a question it will suggest terms you could add based on the tables in the data set. If the question turns out to be extremely useful, you can pin the visualization to the dashboard, making this an easy way to create visualizations for a data set. If you own the data set, you can also add featured questions in the dashboard settings. Q&A uses the names of tables, columns, and calculated fields in the data sets; if the column is called “area” rather than “region,” you need to ask for “sales by area” unless you add synonyms, and table names such as CustomerSummary will make Q&A less natural than names like Customers.
Power BI Q&A works in the Power BI website and the iOS Power BI app. It can work on data stored in an Excel table (or in a database via the on-premises gateway if you enable Q&A for the data set) or you can use Power Pivot to optimize the data set for Q&A. Make sure all the tables in your data set are joined correctly, check data types for dates and numbers, and create the default field set for columns and default label for tables to tweak the columns displayed and the type of graph or chart Q&A will show.
5. Build custom visualizations
Power BI includes a range of visualizations, and you can add more, either by downloading them from the Office Store or by creating your own with the open source Power BI Custom Visual Tool. The Office Store includes vizualizations from Microsoft, such as word clouds, a correlation plot based on R script, and a “box and whisker plot” that highlights outliers, clusters, and percentiles, as well as visualizations created by Power BI customers.
You can also link Visio diagrams to Power BI to use as custom visuals, if you want to analyze progress through workflows and processes. If you have Excel analytics models, you can turn them into custom Power BI visualizations using Frontline’s Analytic Solver. What you get isn’t a static report; it’s a dynamic model that you can drag and drop Power BI data sets onto to simulate or optimize various options.
6. Make the most of AI-powered visualizations
Several of the Power Bi’s interactive visualizations use machine learning to identify insights that would usually require a data scientist. Key drivers help identify and rank contributing factors, such as what influences products to be on back order. The decomposition tree helps you perform root cause analysis, guiding where to drill into the data. Anomaly detection looks at time series data such as line charts and identifies outliers and other anomalies, and suggests explanations. Smart Narratives can pull out key takeaways and trends and wrap them with autogenerated text to build data stories for you.
7. Perform real-time analytics on streaming data flows
Most BI is done on data extracted from a database at scheduled intervals. If you want to analyze data from ecommerce sites or operational technology systems that have sensors, you need access to real-time streaming data. Usually that requires some development to extract the data, but streaming dataflows in Power BI can connect to Azure Streaming Analytics, enabling business analysts to combine batched and streaming data in the same reports to find exceptions, trigger actions, and react more quickly to changes in physical systems.
8. Turn on Teams integration
If your organization spends most of the day in Teams, bringing Power BI reports to where everyone is working (and talking about work) makes it more useful. According to Microsoft, usage of data in Power BI almost doubles when the app is pinned in Teams. If the IT organization has invested time and money rolling out Power BI, enabling Teams integration gets more out of that investment.
9. Curate data for use in Excel
If you share data in Power BI, it’s also available for people to use inside Excel. Power BI can also power data types in Excel, giving you a single, authoritative source of data for entities such as customers, suppliers, products, and other business information that will be used across the organization. You get a shared source of truth and Excel users don’t have to learn Power BI to take advantage of it. They can type in information they want to look up such as a customer name, mark the range, and click on a tool tip to insert new columns from the data set to work with in Excel.
10. Drive machine learning from Power BI
Power BI’s Dataflow helps you automate data preparation and enrichment, making Power BI a good place to keep data sets that will be used for machine learning. Its integration of Azure Machine Learning AutoML means business analysts can also take advantage of machine learning without needing a data scientist — or an Azure subscription. Define what you want to predict, such as whether a product will be out of stock, and AutoML suggests what columns of data to use for the model, selects and tunes the algorithm automatically, and includes the performance and reliability of the model created, along with what features influence the predictions it makes for which products are most likely to be out of stock at particular distribution centers.
11. Combine Power BI and Power Apps
You can embed Power Apps into Power BI reports and set up Power Automate workflows from inside Power BI. So if there’s an action that makes sense to take after getting insights from data, such as adding a customer to an email marketing campaign or making a budget request, you can put the app or flow for doing that in the report where you get the insights — and the filters and selection you make in Power BI carry over to the app or workflow. For mobile users who are more likely to be working from a Power app, you can embed the Power BI report into the app instead.
12. Fit more data into executive dashboards
Different BI users need different levels of information in their visualizations. Managers and business analysts may want a lot of details, but if your executives are tracking 20 or 30 key metrics for multiple regions around the world, it’s better to present that at a glance with a simple view that shows the target and the actual figure rather than a more complex visualization. That way you can look up information quickly in a meeting without getting lost in too many charts and figures. The Power KPI custom visualization combines multiple report types into a single tile.
13. Use goals to build out scorecards and OKR boards
Making a data-driven culture effective means using data to measure how well decisions are working out for the business. Instead of paying for a specific tool to build dashboards for tracking performance and achievement on key metrics, use the Goals hub in Power BI Premium to connect scorecards to Power BI reports. For goals such as revenue, sales, hiring, or user numbers, you fill in when you need to achieve the result by, how you measure it, and select the relevant data points on a chart in a Power BI report. As well as seeing progress in the Goals hub, you can also use Power Automate to trigger alerts or schedule meetings if performance towards a goal is falling behind.
14. Use information protection for sensitive data
When you’re putting confidential company data in Power BI, CIOs and CISOs can make sure only the right staff have access by applying the same Microsoft Information Protection sensitivity labels as in Office, SharePoint, and other tools. Those labels enable auditing, enforce access in Power BI, and follow the data if it’s exported to Excel or PowerPoint for end-to-end data leakage protection.
15. Power BI is for IT data, too
Power BI isn’t just for business users, as you can use it to visualize data for IT monitoring tools. Power BI’s solution template for Azure Activity Logs uses an Azure SQL database and Stream Analytics to collect logs and display them using prebuilt Power BI Desktop reports, so you can look at trends in usage and problems. There’s also a set of prebuilt Power BI reports for the Intune Data Warehouse that shows device details such as configurations and compliance state, and a solution template for System Center Configuration Manager with a dashboard that covers client and server health, malware protection levels, software inventory, and which devices are missing updates. There are templates for a range of other tools, and you can build your own dashboards and reports for other tools, as long as you can get the data into a SQL Server or Azure SQL database.
