Get Top 5 tips (​with examples)

Power BI offers many advanced functions for data analytics but you do not need to be an expert to use it.

In fact, it can be very useful, regardless of your knowledge of data analysis – which is what it should be! Here are 5 tips on how to make the most of Power BI reports.

Power BI is a great tool for data visualization and (some) data transformation, no doubt about it. Over the last years of its development, it gained many great features and capabilities.

There are also many resources available on the Internet if you’re looking for training materials (which is not what you’ll find here). I assume you have touched upon this technology at least a bit. At the same time, you’re probably not a hardcore analyst, as you would most likely know all these tips already.

This article will not tell you how to do all the things you possibly could with Power BI. In fact, you should try it for yourself, it’s very intuitive and allows you to build very advanced visualizations.

Once you stumble across a challenge, you should look up answers online on how to approach it. Or, you can check out our data visualization pack.

That’s exactly what we did here at Predica – we’ve built a companywide analytical reporting tool that anyone in the company can use without extensive training. Not to be modest, I would even say it took us very little time to achieve it.

However, we have already invested much effort into building and maintaining proper data sources. Therefore, I’d like to share some experiences we’ve had. I will also share little hints we use in creating reports both for us and for our customers.

Tip #1: Simplicity – don’t get carried away with visualizations

Following the idea of delivering a message… There is an increasing number of visualizations available in Power BI which you can get from AppSource. Some of them are pretty complex. They can show you the relations between data elements in an unordinary way that can make sense… quite rarely (for example, if you’re a hardcore analyst).

Selecting the right chart for your data

For most of us ‘ordinary people’ – and I’m saying, probably 98% of us – simple means better, easier, clearer, [put here whatever you think suits]. So, focus on simplicity!

In most cases, a (boring) bar or line chart will surely suffice. Also, don’t fear the old-school and ‘ugly’ tables – they are still the best way to present raw data, which is sometimes all you really need (and what you keep using Excel for!).

For example, I try to avoid pie charts and treemaps for a very simple reason – you cannot see the difference between pie fields that have similar values.

Let’s try to report the sales volume per region – try telling whether red or orange is bigger or by how much they differ:

Isn’t this clearer?

Case closed.

The general rules of thumb about visualizations

Remember that people usually read from top left to bottom right, so put the most relevant stuff (KPIs?) where users go first

so put the most relevant stuff (KPIs?) where users go first Vertical bars – for general data display. A void rankings, use sorted data, it’s easier to read

for general data display. void rankings, use sorted data, it’s easier to read Horizontal bars – these are actually best for data rankings

these are actually best for data rankings Line – usually for time series when you need to compare multiple series of data, for single bars it works just as well

usually for time series when you need to compare multiple series of data, for single bars it works just as well Bar/line mixed – to present two values of different types (like money and percentage)

to present two values of different types (like money and percentage) Bubble – to present 3 different number values (two axes and bubble size).

Enjoying your read? Join over 2,500 professionals who are one step ahead with our curated industry insights! Sign up

Tip #2: Context – interrelations between elements

One of the coolest features of Power BI is its cross-filtering capability. It means that once you have two charts with connected data next to each other, when you click on an element on one, the other will be filtered based on what you clicked.

This greatly helps with the data comparison, kind-of-visual drill-downs, and simple analysis.

Using filters in Power BI

But what might not be so obvious at first sight, is that you can actually use three ways of filtering and connecting data to make your analysis experience better and easier.

Let’s consider the project management example. You may be interested in seeing the time reported by people (top bar in the below example) and the time reported each month (the bottom bar). There you can see the different behaviors the interactions provide.

Types of interactions:

1. None

No filtering happens between elements. Use it if you want to display data as it is so that it’s not affected by users’ behavior. In the example – clicking on the bar in the top chart does not influence data displayed on the bottom:

2. Highlight

The filtered value is displayed in the context of the total. Use it when you want to show how much of the total the selected element forms. In the example – clicking on the bar in the top chart fades out the bottom chart. Only the part of the bar which is applicable to the clicked element remains highlighted:

3. Filter

Displaying the actual filtered value. Use it when you want to see what actually hides behind the selected element. Here you are interested in the detailed data and not its relation to the total. In the example – clicking on a bar in the top chart filters out the bottom one and leaves only the data applicable to the clicked element:

So, depending on the context in which you are viewing your data, it may have a significant difference on which relationship you select.

Additionally, when there’s a lot of data elements, it might greatly influence the ease of use of the report, especially for not advanced users (who we usually create such tools for).

Find more info about creating interactions between visualizations here.

Tip #3: Divide and conquer (or slicing and dicing) – filters

It’s the most basic concept of data visualization, yet you might still be surprised by how many filtering possibilities there are in Power BI reports. Here are 5 obvious ones.

Basic report filters panel:

Visual level filter – filters data only at the selected visual level which can be particularly useful if you want to have some background (not visible in the chart) data used only for filtering Page level filters – apply to all elements on the page Report level filters – apply to all pages which can be particularly useful when a user is supposed to journey through the pages to see the data in the same filtering context, but with a different view presented on each page. Once you select the filter and move to the next page, the filter stays selected which allows you to see the data in the same context:

And two in-canvas filters:

Slicers (in-canvas filters) – filters available as single or multiple selection checkboxes or dropdowns. I haven’t found them particularly useful. They take up the canvas space and, considering cross-filtering capabilities of most visualizations, do not provide much value added. Also, like the page level filters, they work only on a particular page. This in the majority of cases I worked with was rather limiting. The reason is that when you go to a different page, you lose the context of the data you worked with. Cross-filtering (as described in the previous point) – the additional idea behind these filters is that they can be used instead of (somewhat dull…) slicers to include additional information (selected measure). If instead of, for example, a checkbox list you create a vertical chart, you can use it just for filtering – just click the bar to filter out everything else:

Again, let’s consider the project management example. You can think of having a multiple page report with pages giving you an overview of hours (like in the interactions example) or details of time reported under particular tasks (as in the above example).

So, if you use in-canvas filters, you need to select the project you are interested in on each page individually. However, when you use report level filters, the project is still selected when you browse through different pages. Now, imagine having a report with 7 or more pages… try it yourself and you will see how much sense it makes.

Tip #4: High or low perspective – hierarchies

Hierarchies are a great way of showing data analytics on various levels of granularity using the same visualizations. For example, in a project management domain, a program manager may be interested in project(s) progress and time reported per month, whereas a project manager could be interested in a weekly level to look into what is happening more closely.

Obviously, you can create different reports for each of them. However, you will then end up managing and supporting a large number of such cases. Alternatively, you can be clever and design a report in a way that can be used by both. And this is where hierarchies come in handy.

Using hierarchies in Power BI

There are three ways to use hierarchies:

They can come from the data source (typically OLAP/Tabular-like), so basically present in the data model

(typically OLAP/Tabular-like), so basically present in the data model They can simply be based on date and time data – here Power BI does a nice thing for us and allows us to present any time data as a Year/Quarter/Month/Day hierarchy ( more here )

here Power BI does a nice thing for us and allows us to present any time data as a Year/Quarter/Month/Day hierarchy ( ) Or you can put more than one dimension in the visualization. It doesn’t make them visible but allows us to drill from one to the other.

Once you have some, just notice the small arrows that appeared in the corner of the chart which you can use to go up and down the hierarchy levels:

The same visualization and report is used to achieve different perspective views.

Since it’s easy and fast to create reports in Power BI, you can be tempted to create many of them just because you can. But think of the poor users who will be using these reports and how they can get confused when they get tons of reports or pages showing similar things…

Once you let people into a tool like Power BI, the effect could easily end up being a Picasso-like analytical painting with many colors but really not much value to it. In a matter of seconds, you can produce any number of beautiful charts showing any number of data pieces like a well-operated assembly line.

Yet, Power BI reporting canvas is like PowerPoint slide – no scrolling or pagination can make you feel… limited. But that’s the whole point! The time you spend in Power BI should be spent on trying to fit and visualize the information in that space. It should be clear and easy to digest by potential users at a first sight.

It is especially important when you consider that Power BI has two display areas:

Dashboard – the primary point where users go to, but with no filtering or interactions. Dashboard tiles are only links to underlying reports and their purpose is to present the current status of things

– the primary point where users go to, but with no filtering or interactions. Dashboard tiles are only links to underlying reports and their purpose is to present the current status of things Reports – analytical spaces with all the interactive capabilities. Their purpose is to dig into data details to understand the reasons why certain things happen

Consider this sales opportunities example from Microsoft:

Feeling dizzy? What do we really want to see here?

Luckily, this is only the demo dashboard presenting product capabilities rather than anything of real use. This is a bad practice example as all tiles in this dashboard show pretty much the same data (opportunity count and revenue), just from a different angle. This makes it more analytical than the status view. Consider how this can be simplified to put focus only on the important things – the actual opportunities’ number and volume:

Not only can you see it better, but you also have more space to add other (meaningful!) things. If you want to know more about the data displayed, you just need to click on any of the tiles to get the report where you can see all the data from the original dashboard:

So, the rule of the thumb is: include less, but only the meaningful stuff. Remember that the information you want the user to get is the most important. It’s not about the overwhelming number of data views in all possible dimensions.

It should be clear at first sight whether there is a problem or not, whether you need to investigate further or have a peaceful moment to grab a cup of coffee.

Next steps

The concepts presented above are very basic advice that you can use when creating reports that should be simple and easily understood by regular users. I collected them here as they are also built on our experiences from designing analytical reports for our company.

They are now successfully used by people across project management, finance and development practices. All thanks to simplicity, focus on the users’ needs and spending more effort on figuring out what should be the most efficient way to tackle the particular piece of data and then create the report.

Don’t forget to check out the Power BI blog to be up-to-date with new features and releases.

Curious to see some more examples? Check out our customer stories where you can see how clear and customized reports make work easier for people across industries:

And remember: it’s easy to create Power BI report, but it’s a little harder to create a meaningful report. Contact us to make sure you only have the best ones!

Performance in Power BI

Weitere Stellschrauben für mehr Performance in Power BI

Sollten diese generellen Einstellungen die Performance nicht verbessert haben, gibt es noch einige Optimierungsmöglichkeiten.

Sinnvolle und effiziente Datenanbindung in Power BI

Zunächst kann die Datenanbindung in Power BI auf verschiedenen Wegen erfolgen. Beispielsweise sind der Import, die Erstellung einer Direct Query oder ein zusammengesetztes Modell möglich. Hier gilt es grundsätzlich zu klären, welche Transformationsschritte in der Power Query angewendet werden, welchen Umfang meine Daten haben und wie viele Datenquelle ich habe.

Ein kleines Beispiel aus der Praxis: Das Exportieren von CSV-Dateien aus einem ERP-System und dem darauffolgenden Zusammenführen in Power BI mittels Parameter und Funktionen ist natürlich langsam und nicht effizient. Hier zeigt sich, dass nicht immer Power BI der Performance Bottleneck ist. Insofern ist es oft die Art und Weise, wie und wo Daten entstehen beziehungsweise bereitgestellt werden. Demgegenüber wäre das in einer idealen Welt ein gut strukturiertes, am besten nach Data Vault 2.0 aufgesetztes Data Warehouse mit verschiedenen Data Marts für verschiedene Fachabteilungen.

In der Wirklichkeit sind es fünf Excel-Tabellen, die mit einem ERP-System wie SAP, einem CRM-System wie beispielsweise Hubspot und dem eigentlich nicht mehr vollständigen, weil in die Jahre gekommenen, Data Warehouse zusammengeführt und angebunden werden. Kurzum sorgt das für viel Störungspotenzial und drückt die Performance.

Neben der Datenanbindung als eine Hauptquelle für Performance-Probleme in Power BI wenden wir uns nun den eigentlichen Berichten zu. Schließlich gibt es auch hier einige Einstellungen, die wir zur Optimierung nutzen können.

DAX Studio

Um die Performance der Berichte zu optimieren, gibt es mit DAX Studio ein dediziertes Tool. Damit lassen sich die einzelnen Queries hinter den Measures und Visualisierungen auf ihre Performance hin analysieren und optimieren. Der kostenlose Download sollte in jedem Fall einmal genutzt werden, zum Beispiel um das PBI-Modell einzubinden und die verwendeten Queries zu analysieren.

15 power tips for Microsoft Power BI

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.