7 power tips for Microsoft Power BI

Power BI is Microsoft’s interactive data visualization and analytics tool for business intelligence. You can use it to pull 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, or drill in and (literally) ask questions about your data. You can create rich reports or embed dashboards and reports into reporting portals you already use. The dashboards, reports and visualizations you can create go far beyond bar and pie charts, but you don’t need to be a designer to use them. Here’s how to get more insights from the information you already have, in more areas than you might expect. [ Deliver deep insights with the 7 keys to a successful business intelligence strategy and find out why you’re failing at business intelligence. | Get the latest business intelligence and IT strategy analysis by signing up for our newsletter. ] 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 visualisations — and not just Microsoft ones like Dynamics 365 and SQL Server. 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, or even GitHub, 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, try the new Timeline Storyteller custom visual for Power BI. 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; 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. Software Visualizing data with Power BI (3:49) 3. Explore ‘What-ifs’ You can compare different 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 – setting the data type, minimum, maximum and increments. That creates a calculated measure that you can reference in other calculated measures; 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 that 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 real time Instead of designing charts and reports, use the natural language features of Power BI to ask questions and get visualizations in response. You can specify the way the data is 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’d need to ask for “sales by area” unless you add synonyms, and table names like CustomerSummary will make Q&A less natural than names like Customers (even though Q&A would know that’s the table you want if you ask about “customer summaries in Chicago” because it can break words up and understand plurals). 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. Custom visualizations Power BI includes a good 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 (which uses CSS, TypeScript and NodeJS). The Office Store includes vizualisations from Microsoft, like word clouds, a correlation plot based on R script, chord charts to show interrelationships in a circular matrix, the “box and whisker plot” that highlights outliers, clusters and percentiles to show data that might otherwise get lost in summarized figures like averages, as well as visualisations created by other Power BI customers. You can also link Visio diagrams to Power BI to use those as custom visuals, if you want to analyze progress through workflows and processes. If you have Excel analytics models, you can use Frontline’s Analytic Solver to turn them into custom Power BI visualizations without having to design the custom visual in JavaScript. What you get isn’t a static report; it’s a dynamic model that you can drag and drop different Power BI data sets onto to simulate or optimise different options. 6. 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, maybe 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. 7. Power BI is for IT data, too It isn’t only business users who have large amounts of information they need to distil down for insights; you can use Power BI to visualize data for IT monitoring tools. The Power BI solution template for Azure Activity Logs uses an Azure SQL database and Stream Analytics to collect logs and display them using pre-built Power BI Desktop reports, so you can look at trends in usage and problems. There’s also a set of pre-built Power BI reports for the Intune Data Warehouse that shows device details like 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.