Rules to Better Power BI - 18 Rules
Want to get Power BI working for you? Check SSW's Power BI Consulting page.
Power BI offers a variety of visualizations, each suited for different types of data and insights. Choosing the correct visualization is crucial for effectively communicating data stories.
Here's a guide to understanding when to use each type of visualization provided in Power BI, according to the visual selector interface.
An Area Chart would be chosen over a Line Chart when you want to highlight the cumulative magnitude of values over time, showing not just the trend but also the volume beneath the trend line, emphasizing the total value across the timeline.
e.g. If you are looking at the total revenue generated by a product over the same period, an area chart is better than a line chart because it not only shows the trend of revenue over time but also gives a sense of the total revenue accumulation, providing a visual impression of growth beyond just the trend line.
Area charts are excellent for stacked charts because it’s a simple and clear way to clearly portray the cumulative nature of the data. For example, if the above example was visualized with a line chart, it wouldn’t be immediately apparent to the user that the values are added together, not compared against each other.
Choose a pie chart when you need a simple, classic representation of each category's contribution to the whole, where the focus is on relative sizes of the parts to the whole.
Opt for a donut chart over a pie chart when you want to include additional information in the center, such as the total value, or to improve readability when comparing multiple pie-like charts.
A treemap is preferable to a pie or donut chart when you have hierarchical data and need to show part-to-whole relationships across multiple levels in a compact and space-efficient manner.
This visualization is suitable when you need not only to plot data points on a map but also to leverage Azure's cloud-based location services for more in-depth geographic analysis, such as calculating routes, visualizing traffic conditions, or creating heatmaps based on the intensity of activity in different areas. It's a powerful tool for scenarios requiring a combination of mapping and intricate spatial operations.
ArcGIS Maps in Power BI is suitable for scenarios that require more than basic mapping, such as thematic maps, heat maps, and demographic layers. This visual is particularly useful when geographical context and spatial analysis are key to understanding and presenting your data, such as in urban planning, environmental monitoring, or market analysis.
For example, if you're trying to identify if there's a relationship between sales volume and advertising spend, a scatter chart can plot each point of data in the two-dimensional space where one axis represents sales volume and the other represents advertising spend.
This visualization is beneficial when you want to explore potential connections or correlations between variables, identify outliers that don't fit the general pattern, or even to see the distribution and concentration of data points.
If a 3rd dimension is added (as above) it's represented by the size of the bubbles. this sometimes known as a Bubble Chart.
The clear visualization of incremental changes helps identify how individual components contribute to the total outcome, making the waterfall chart a powerful tool for detailed, step-by-step analysis.
In this example, a car retailer is tracking the sales team's average sales per month. The gauge needle represents the sales goal of 140 cars sold. The minimum sales average is zero and the maximum is 200. The blue shading shows that the team is averaging about 120 sales this month. They have one more week to reach the goal.
This space efficient visualisation shows the target number, the current number, the variance %, and the trend of the number over time.
You might choose to use a matrix over a table in Power BI when you need to display data with two or more dimensions, allowing for a more complex hierarchical structure with expandable row and column headers, and when summarizing data with built-in aggregations, like sums or averages, is necessary for a condensed view.
The Key Influencers visualization helps in discovering patterns in the data, such as which variables most contribute to an increase or decrease in your target metric. It is particularly useful in scenarios where you want to perform a lightweight and interpretable form of analysis to drive business decisions, such as understanding customer satisfaction drivers or pinpointing reasons for sales trends.
The decomposition tree is effective for drilling into dimensions of data to see how they contribute to the overall metric, allowing for dynamic exploration by users who can choose the factors to analyze at each level of the tree. It is particularly useful for ad-hoc exploratory analysis and root cause determination.
The Q&A visual is particularly useful when users may not be familiar with the underlying data model or when they wish to explore the data without pre-defined reports or dashboards. It's a powerful feature for creating a conversational data exploration experience within Power BI.
Smart Narrative is ideal for creating data-driven narratives that provide context, explanations, and annotations, enhancing the report's storytelling aspect. This feature is particularly useful when you want to provide written explanations alongside your data or to offer automated interpretations of complex visualizations for report viewers.
The Metrics visual can combine numbers, charts, and conditional formatting to provide a comprehensive snapshot of performance, making it ideal for dashboards that executives and team leaders use for quick status checks and decision-making.
Paginated Reports are ideal for creating highly formatted, multi-page documents that can be exported to formats like PDF and Word, often used for regulatory filings, invoice generation, detailed financial statements, or any scenario where the layout and format are as important as the data itself.
The Power Apps visual allows you to bring the capabilities of custom apps into your dashboard, enabling users to perform tasks or input data directly from the report. This is particularly useful for creating a seamless workflow where users can act on data insights without leaving the Power BI environment, such as updating records or triggering business processes.
The Power Automate visual allows you to set up automated workflows that can be triggered directly from your reports. This is ideal for scenarios where immediate action is required based on data changes or thresholds, like sending alerts, integrating with other services, or initiating business processes in response to data-driven events.
- Use when: Accessing additional visuals not shown directly on the visualization pane or custom visuals.
Remember, the choice of visualization should not only depend on what looks good but also on what communicates the data most effectively to your audience.
Tip: Always preview your data with different visualizations to determine which one best tells the story of your data.
Your reports and dashboards should tell the right story to your end users. They should be able to get the gist of the report at a glance and not have to work at trying to understand what it means.
Visuals in themselves are neither good nor bad. A visual that users love on one report could be a terrible choice on another report. Power BI comes with quite a few built-in visuals and they are adding more to this all the time. However, sometimes you may need something more than the standard offerings in which case you have 3 options.
- Go to AppSource (previously Marketplace) and choose from the many free/paid offerings
- Use the Charticulator visual in AppSource to create your own custom visual either using templates or DIY using the UI
- Create your own custom visual using custom code with React, Angular, R, etc
Using the pre-built visuals in AppSource saves time and money and should work in most cases. The Charticulator visual has now been integrated in to AppSource which also gives you more flexibility and options. Creating custom visuals from scratch using D3 or similar libraries can be time-consuming. One relatively easy way to add more visual options to your toolset is to create your own visual using React and then use free 3rd party charts to add the extra sizzle. Check out this video to explore this option. https://www.youtube.com/watch?v=eJ6uHwaGJRM
For more inspiration on using amazing visuals have a look at some of these urls:
Doing version control with Power BI reports used to be problematic. The primary way of doing this was to commit the pbix file into the repository using source control tools such as Visual Studio Code (VS Code). However, this has some drawbacks:
- Data itself gets saved to source control, which is bad as it could be large
- Unable to see what has changed
- Version control process is not user friendly for non-developers
Microsoft has addressed these issues through the introduction of:
- Requires either Fabric capacity or a Power BI Premium per User license
- Currently only integrates with Git repos in Azure DevOps
- Power BI Desktop projects
The following video from Microsoft Build 2023 provides an overview of this.Video: Empower every BI professional to do more with Microsoft Fabric | OD06 (Watch from min 5:00 to 13:00)
At a high-level you can set up version control as follows. Click on the links to get more detailed instructions on Microsoft Learn.
- Connect a workspace in Power BI Service with a branch in a Git repo in Azure DevOps
- Commit changes to repo through the Power BI Service
- Update the workspace from Git
Committing a report to the repo in this manner saves it as a Power BI Desktop Project (PBIP). A Project no longer contains a pbix file. It instead decomposes the report into the following artifacts.
- A Dataset folder, which contains files and folders representing a Power BI dataset
- A Reports folder, which contains the report settings, metadata for custom visuals, etc.
You now have two options to edit the report and commit changes.
- You can directly edit the report in Power BI Service, and then commit to the repo via Power BI Service as explained here. This is the option that non-developers may prefer as they generally don't modify the data model. Further, the version control user interface is nice and simple.
Clone a local copy of the repo on your PC by using version control tools such as VS Code, and use Power BI Desktop to edit the report.
- The PBIP Reports folder contains a file called definition.pbir, which is what you would open to edit the report in Power BI Desktop. This allows you to edit both the report and the dataset. You may have to first enable PBIP in Power BI Desktop by going to File | Options and settings | Options | Preview features, select the checkbox for Power BI Project (.pbip) save option.
- You would then use VS Code to commit any changes back into the repo. This is no different than committing conventional source code. Since PBIP decomposes a pbix into component files, many of which are textual, you can compare files across commits.
- Note: PBIP folders do not by default contain any underlying data. So when you open a definition.pbir file the visuals may show as empty. Once you refresh the report Power BI Desktop will download a copy of the data into a file called cache.abf which gets stored in a ".pbi" folder inside the Dataset folder. This file should not be saved in version control. You can create a .gitignore file to prevent Git from committing it to the repository.
You can save report under My Workspace and share it with your team. This is bad because if you leave your company, your report is gone!
It's better to save reports under Group Workspace and share it with the team.
- Group work space requires all users to have pro license ($10 / month)
- Group work space cannot share report and dashboard via embed link
- Until Microsoft fixes PowerBI's group sharing, it's better to use My Workspace with a shared account (i.e. alias email)
Using colour in reports can bring them to life, or else make them confusing and noisy, so make sure youre intentional with them.
Remember that some colours have a shared understanding of their meaning:
- Red means pay attention or danger
- Green often means good
- Traffic lights can be used for showing 3 levels
- Cold, Warm, Hot should have appropriate colours that make sense without having to look at the legend
Edward Tufte is often quoted as saying that, on a report where you want to draw the eye to exceptions or out of bounds data, colour should only be used to highlight what you want the user to see.
Make sure you stick with one colour palette, so if you use one pastel colour, make them all pastel. If you have one bold colour, make them all bold
Just to be difficult, it is worth noting that red/green colour blindness is the most common type, which may make standard semantic colours sometimes less desirable. In this case, use blues and oranges for contrast instead.
Many reports are expanding on what could be expressed as a single number. When this is the case, make sure that number is clear and bold at the top right of the report.
Ideally this should be a single number, not a group of numbers. If there is more than one, make sure one is chosen as the main one and put the others in a less prominent colour, size, or position.
Leveraging AI tools for critiquing and enhancing reports and dashboards can significantly improve their quality and effectiveness. Free AI tools such as the Report Enhancer GPT, can be used for this purpose, which checks your report against the International Business Communication Standards (IBCS).
The International Business Communication Standards (IBCS) are widely recognized best practices for the design of business reports and dashboards. These standards focus on:
- Consistency: Harmonizing design for easy understanding and comparison.
- Simplicity: Reducing to the essential to avoid non-data ink.
- Focus: Directing attention to the important information.
- For Public Reports: If your reports or dashboards are publicly accessible, simply provide the AI tool with the link. The tool will analyze and offer suggestions based on the IBCS standards.
- For Private Reports: For internal reports, take a screenshot and provide it to the AI tool. This GPT has had "Train on this data" disabled, so the information will not end up in ChatGPT's long term knowledge.
- Upload or Link: Go to Report Enhancer and provide your report or dashboard via a link or screenshot.
- AI Analysis: The AI tool will analyze the design, layout, and presentation of your data.
- Recommendations: Receive feedback and recommendations aligned with IBCS standards.
- Implement Changes: Apply the suggestions to enhance clarity, readability, and effectiveness.
By incorporating AI tools in line with IBCS standards, you can significantly enhance the quality and effectiveness of your business reports and dashboards. This approach not only streamlines the process but also ensures that your reports meet high standards of clarity and professionalism.
Tip: Always review AI suggestions critically and ensure they align with your specific reporting goals and audience needs.
The problem with the standard File | Publish to Web options, is it always goes to the same tab of a report, but if you want to send a link to a specific tab, you have to do one more step...
Do the above as usual, and then go to that published public report, and navigate to the tab you want to send people to.
At the bottom right hand corner of the screen, there is a share icon. Click this for a sharable URL for that tab (people can still navigate to other tabs as needed, but will start on the one you've chosen.
The Power BI Portal can be customized with custom branding. The items that can be changed to make Power BI fit into an organizations brand are:
- Cover Image
- Theme Colour
To make these changes navigate to: Admin Portal | Custom Branding
Make your changes and save. That's it, you now have a branded Power BI Portal.
It's tempting when you create a Power BI report to put slicers wherever they fit, but this can lead to an incosistent experience for users.
A better solution is to always put your slicers at the top (and towards the right) whenever possible, and also to replace any non-essential slicers with filters instead, as the filter pane fully collapses on the right hand side, and the control to expand it is already at the top right hand corner of the screen.
The default sort order for most visualizations is alphabetical, but you should almost always change this.
Alphabetical sorting is really only good if you're specifically looking for one item on the X axis, but it's terrible for seeing ranks, trends, or proportions.
Monitoring Power BI report usage provides the organization with a valuable insight into the effectiveness of their reporting strategies. By tracking how reports are being accessed and engaged with, businesses can make data-driven decisions to optimize their content, allocate resources efficiently, and enhance the user experience.
This information is essential for understanding which reports are delivering the most value, enabling organizations to prioritize their efforts, improve content relevance, address performance bottlenecks, and align their resources with actual demand. Moreover, monitoring report usage supports security and compliance efforts by detecting unauthorized access patterns, ensuring data protection.
You have 2 options to view the Usage Metrics Report for an individual report:
- Inside your Workspace for the Report, that you are interested in, click “More Options (…)” | “View usage metrics report”.
- Inside opened Report in the command bar click “More Options (…)” | “Open usage metrics”.
If you're viewing a Usage Metrics report for the first time, Power BI may initially open the old version of the this report. To access the enhanced Usage Metrics report, toggle the "New usage report" switch located in the command bar.
Usage Metrics Report dataset contains data for the last 30 days and refreshes daily.
The report contains 4 pages:
- Report usage – Shows such information as number of report open requests and views per user or per page
- Report performance – Shows trends of Open Report actions
- Report list – Shows the list of all reports in the workspace and their metrics
- FAQ – Shows the answers to frequently asked questions about Usage Metrics Report
While Usage Metrics Report already provides valuable information, it can be potentially further enhanced.
If you want, for example exclude certain users or reports from these metrics, it can be achieved modifying this report.
If your workspace does not already contain a customised Power BI Report Usage report, you must first save a copy of your Usage Metrics Report by clicking File | Save a copy. You will then need to input the name for a new report and select the workspace where you want to save it.
The new Power BI Report Usage report will be visible to the users with the Viewer role, unlike the default Usage Metrics Report.
The report will have Edit button available in the command bar. It allows you to go into the Editing view, as with any regular report, and change filters, add new pages and more.
Users can quickly subscribe to emails of the report pages that matter most. Once subscribed, Power BI will regularly send screenshots and a link for the report page directly to your inbox.
Video: Power BI Email Subscriptions - Many people love getting reports in their inbox monthly
There are 2 ways to integrate Power BI with SSRS (SQL Reporting Services) that will allow you to be able to find all of your relevant reports from wherever you look for them
In SSRS 2016, you can list Power BI reports as if they were SSRS ones.
Figure - Good Example: Power BI dashboards can show charts from SSRS reports, and link through to them when clicked.
When you run into a wall in Power BI and feel like you've exhausted the out of the box functionality, that when it's time to investigate what a bit of DAX can do for you.
There are 2 different things you can do with DAX, create a Measure or a Calculated Column.
- Stored in the database
- Often used to filter/group data
- Computed on aggregates of values
- Computed at query time
- Often used to give a numerical metric
GroupingColumn = if (value<x, small, if(value<y, medium, large))
Figure - Good Example: Nested if statements are a great way to split up your data into groups
When creating a Power BI connection to Dynamics 365, the first thing that comes to mind when searching for a connector is to search for Dynamics 365, seems logical enough right?
When connecting to Dynamics 365 data always use the Dataverse connector (if it is available). Your system admin will need to tinker with some settings to enable this support, but it's simple and easy enough to do, here's the link: https://docs.microsoft.com/en-us/powerapps/maker/data-platform/view-entity-data-power-bi.
Once enabled instead of using Dynamics 365 (online) connector use the Dataverse connector.
The advantages of using the Dataverse connector are:
- Supports both Import and Direct Query (Direct Query means live reporting 🙂)
- Dataverse is built on top of TDS (Tabular Data Stream), meaning it should be much faster than the WebAPI connector
- Potential to write custom SQL queries for data sources
The disadvantages of using the Dataverse connector are:
Knowing when to use SQL Server Reporting Services (SSRS) over Power BI can ensure that you are using the tools most efficiently to help drive your business.
Many organizations see Power BI as a replacement for SSRS due to a lack of major advancements in the SSRS space however while similar they both fill different key needs for organizations and the BI team at Microsoft has invested a lot of time into improving the SSRS offering.
SSRS is better for "Pixel perfect" reports that you can design exactly to your specifications, whereas most Power BI visualizations only have a finite amount of visual tinkering possible.
SSRS provides a stronger ability when it comes to static representational reports, like invoices, monthly reports, mailing lists...
Power BI however is better for a dynamic interrogation of data as it currently stands so this can allow power users to drill into live data to identify trends.
Feature Power BI SSRS Printing - ✅ Mobile app ✅ - Connectors ✅ ✅ Language support ✅ ✅ Realtime data filters ✅ - Report parameters - ✅ Embedding support ✅ -
Figure: Power BI/SSRS feature comparison
The emails exchanged within your company are more than just communication tools, they are a rich source of data. By analyzing and interpreting this data, you can:
- Gain insights into a company's operations
- Detect bottlenecks (nobody likes to get blocked)
- Better distribute workload
- Make strategic decisions
Emails should follow a set of email rules that make communication consistent and structured. When emails have standard format and content, data analysis is possible.
Collecting data is great, but if you're not creating actionable insights, you're missing out on the real value. Here are some examples of actionable insights you can gain from analyzing company emails:
Through analyzing company emails, one can assess whether tasks and responsibilities are allocated evenly across teams. Some rules capturing these emails:
Email analysis can reveal insights into project timelines, milestones, and communication patterns. Some rules capturing these emails:
- Do you create a Sprint Review/Retro email?
- Teamwork - Do you manage up? (Give a recommendation)
- Do you escalate key updates and deliverables?
By examining email exchanges with clients and trends in customer enquiries. Some rules capturing these emails:
Finding engineers that talk time frames and $. Some rules capturing these emails:
- Office environment - Do you know how to get approval for a purchase?
- Budgets - Do you monitor your costs in Azure?
Finding people that have efficient meetings. Some rules capturing these emails:
So many problems in business come down to a lack of clear and effective communication. Some rules capturing these emails: