A Power BI report can be easy to create and provide fantastic insights to business growth. However if a senior stakeholder spots any errors they could call your entire data set into question. Underlying calculations and transformations performed on multiple data sources can mean an engaging page of insights is nevertheless an enigmatic black-box, and an ‘oh, can’t trust that’ rumour can be difficult to recover from.
This article provides guidance on building your reports to inspire confidence in their insights, ensure they can be easily audited for accuracy, and remain resilient through changes in authorship.
Branding
Where your report’s fonts, colours, language, tone, and imagery matches that of the organisation’s it fosters trust and credibility with your audience, and owing to its consistency with content they’re familiar with will enhance engagement, boost understanding, reinforce organisational culture, and gain buy-in to the messaging. Though this is generally a subliminal feeling, the importance of it shouldn’t be underestimated.
Branding in Power BI can be achieved in the following order of complexity:
- Customise and save a ‘theme’ using the out of the box toolkit by clicking on View/Themes/Customize theme’:
- Use a third party tool to generate a theme such as PowerBI.Tips, BIBB, or Theme Generator
- Edit the generated json files from either of the two options above directly following the Microsoft Power BI Theme guidance.
- Where you can specify a style in Power BI (matrix column header borders, for instance) and can’t find any documentation for it, create a blank .pbix file and add a visualisation to it with only that style applied; save and close; then change the extension from .pbix to .zip. Extract and open the ‘layouts’ file in the ‘reports’ folder where you’ll be able to see the syntax for that specific style. This can be tricky and risky (Microsoft may change undocumented features without warning) – but worthwhile investigating if Power BI developers across your organisation will be using the theme.
Tell the story
People arriving at your report for the first time should be immediately confronted with the purpose of the page they’ve landed on – why it has been created, and how it might help them. Follow that up with the platforms that have been used to generate the insights.
As well as ensuring the labels, axes, cards, and titles of visualisations on the page are coherent, Insert/Button/Information and drag it to the top right of each visualisation. Under the button settings turn Action and Tooltips on, the enter some text to help users understand what the visualisation is showing and what they might be able to use the insights for:
Standardise the Footer
Adding a footer to your report is a great place for people to find additional information should they have any questions about the content. It could be a single line outlining who the report was produced for, it’s author hyperlinked to their profile, high-level data sources, when the report was last refreshed, and a link to a documentation page.
Documentation
Create a page within every report which documents:
- The report’s data sources, with links to them where possible
- Transformations that have been applied to those sources
- Measures that have been used to calculate metrics
- The entity data model – screenshot the schema view and embed that as an image
- Version history
Add a button in the footer or navigation which links to it.
Commenting for clarity
We have to accept that eventually report authors move on. Ensuring others can pick where they left is crucial to an effective handover. Additionally should you want to collaborate on a report with someone, share it as a template, or submit it to an audit, adding context-relevant commenting can help them to hit the ground running.
DAX
- Add measures to a separate table so they’re separated out and easily distinguishable from the source queries
- Rename any measures or columns from the default so their purpose is clear
- Add comments to complex statements by appending //[comment] to lines where this will help
Power Query (M)
- Group queries, functions, and parameters into folders
- Rename query steps from the defaults so its clear what they’re accomplishing
- Add descriptions to your steps to further highlight what’s occurring in them by right-clicking on the step, clicking ‘edit properties’, and fill in the details:
An information icon will then appear beside the step title, and the description will appear on hover.
- To make our work totally transparent, particularly where a query is complex and people are laying their careers on the line based on the metrics we’re providing, record important query transformations as a video:
- Go through each step in a query so they’re all fully loaded
- Open the Snipping Tool (Windows 11 only)
- Click on the ‘video’ icon at the top, then ‘new’, and draw a box around where the code, steps, and data appear on the screen
- Press ‘record’, go through the steps, press ‘stop’ then ‘save’
- Upload it to an accessible storage location, and add a link to it on your documentation page
Validate your numbers
For people to be able to trust our reports, the numbers need to be watertight – and if they’re not, we need to be able to understand and explain why. So often they’re out – perhaps the quality of the source data is patchy, we’ve a typo in our transformations or measures, a hidden filter is being applied returning the unexpected, or we’ve assumed a column to be l when its I.
A colleague was doing a sailing course, and explained that they’re expected to sail to a location and validate they’ve arrived there using at least three methods:
- ‘My phone says I’m here’, that’s easy
- Triangulating their current location using surrounding features on a map (buoys, hills, towns, and so on) and validating that against where they’re supposed to be
- Checking the water depth matches the depth of their expected location on a map
- The sea current is running in the same direction as their expected location
- The sun is at the right angle given the time and expected longitude
- If it’s noon or night-time, validating the latitude against the expected location’s
I found it a helpful anecdote, as it highlights that there are usually many ways to validate information if we think outside the box. So what methods might we use when validating our reports?
- Make an educated guess at what a metric might be; is the result on your report in the same ball-park?
- Run a back-of-an-envelope hand calculation on the source data to validate numbers based on a small sample of a single metric in the report
- Create a table using raw data that transparently displays how an important metric was arrived at, and include that on your documentation page
- Validate the metrics against other reports that might have been produced, perhaps by the source systems
- Where our data for one demographic is wildly different than the majority’s, can we explain why?
- Check for outliers in the data – is there a reason we need to be mindful of, and are these skewing averages? Might including ‘range’ and ‘median’ metrics be useful where this is a case?
- Before publishing a new version, save the previous version using a standard versioning nomenclature. When our metrics change between versions – for whatever reason – people may need to refer back to the previous version. Understanding why the numbers might be different should be explained on the documentation page under ‘Version history’.
- Create a ‘data validation’ page to allow comparison with source systems; set up alerts to notify stakeholders where discrepancies appear
- Collaborate with a colleague to review and check the metrics in the report. Shared responsibility for it will enhance people’s trust in the output.
- Use third-party tools to optimise and validate your report:
If you’ve identified an data issue, work through the steps outlined in Power BI Troubleshooting: Three Steps to Fix Common Data Issues