This report was the winner of the Complex Data Preparation Challenge for the 2017 Data Insight Summit in Seattle.
The challenge involved taking a number of disparate data sets from the Hawaii Tourism Authority (on visitor numbers to Hawaii) and turning them into a visualization which delivers real insights on visitor trends. A short overview of the report can be seen in this video. and the methodology for dealing with these is outlined here
The report is referenced in the following LDI topics:
- Design a Data Model (in the context of a Dimensions/Measures Granularity Matrix and joining multiple data sets together)
Hawaii Tourism (ZIP File containing PBIX file and source files)
This report was an entry to the Power BI Year in Review contest in January 2018 and was a winner of best features/best use of colour at the Data Viz Shootout Contest at the Data & BI Summit conference in Dublin.
The use of storytelling techniques applied in the report is discussed in this LinkedIn Article.
A brief overview of the report is shown in this video and some techniques for scoping and building this report was presented at the ‘Why, What & How of delivering a Power BI report’ at the London Power BI User Group in March 2018 (Video recording now unavailable).
The report is available on the Power BI Data Stories Gallery here
Technology Trends (Zip File with PBIX Report and links to data sources)
New York Times Bestsellers
This report shows an analysis of the Top rated books in the New York Times Bestsellers List between 1942 and 2017.
It is available on the Power BI Data Stories Gallery here.
The report uses a number of custom visuals and highlights storytelling techniques such as showing a selected genre in blue whilst showing the average of all others (for context) in grey on the Genre detail drill-through page.
NYT Bestsellers (ZIP file containing PBIX Report)
NYC Taxi Analysis
This report tracks 2.3 billion taxi trips in New York from 2000 to 2013. It is used to highlight how to deal effectively with such large data volumes using aggregations/composite models (importing data at a higher level and drilling down into direct query for detail). It also demonstrates an approach to scenario modelling using an integrated PowerApps form and writing scenarios back to a SQL database.
Both of these are shown in the video below and were in a session at the Power Platform World Tour in Dublin (Oct 2019).
Construction Procurement Process Analysis
This report is based on a real-life example of a requirement to track activities associated with a procurement process in the construction industry. The challenge is that there are several work packages (each associated with equipment from a supplier and a set of construction activities) which need to be tracked from the initial design documents through to a Purchase Order being issued. There are dozens of stages in-between these steps each with a plan and actual date. The requirement is to look across multiple cuts of the data (at different reporting dates), understand how long each stage takes, which stage workpackages are ‘stuck’ at, and the impact on the ETA date vs the data that the equipment is Required on Site.
I built this out as a generic example for the community because it illustrates techniques for dealing with data presented in a format that is not particularly easy to work with – a large number of dates against each record – as well as a methodology for getting insights from this kind of data.
I have referenced this in my Storytelling with Data sessions (video below), illustrating how identifying a particular focus in the requirements gathering/scoping template (in this case the change between ETA vs ROS date between the different cuts) leads to a storyboard and presenting the data that will be most meaningful to the particular audience it is aimed at.
It is available on the Power BI Data Stories Gallery here
UK Baby Names from Office of National Statistics
This is a report on data from the Office of National Statistics in the UK (https://www.ons.gov.uk/peoplepopulationandcommunity/birthsdeathsandmarriages/livebirths/bulletins/babynamesenglandandwales/previousReleases) showing the popularity/rank of various Boys and Girls baby names between 1904 and 2017.
I picked this example to produce a community report because the dataset is incredibly simple (just a list of names and rank in each year) though in order to get real insights, it is important to identify a particular audience and the purpose of the analysis/the actions that can be taken from it. In this particular case, I picked an example audience of manufacturers of personalised baby good products who need to ensure they are keeping on trend with popularity of baby names (I also made up some sales data to illustrate the point!).
I have demonstrated a number of storytelling techniques in this report, including showing context for numbers (e.g. comparing two periods in time, showing current rank vs change in rank over time) and highlighting only the most important information (e.g. using a slope graph to show the change between two periods).
This was also an example referenced in my Storytelling with Data sessions (video below).
It is available as a report on the Power BI Data Stories Gallery here
Income Statement with Ratio Analysis
This report shows a completed example of a flexible, dynamic and attention-focused Income Statement Report. It was built for and demonstrated at the inaugural Power User Days Finance event in London (July 2019) which brought together finance professionals who are interested in learning more about how the Power Platform can help with their role.
There are a number of advanced Power BI features demonstrated in this report, including dynamic conditional formatting and parameter tables/dynamic measures. In addition, it highlights a number of storytelling techniques including highlighting the key metrics with context for users to gain insights at a glance and using chart interactions to allow users to drill into detail/find actionable insights.
I have written a series of articles on the Power BI Community Blog which outlines in detail how to build out the first page of this report. This series is available here
The report is available on the data stories gallery here.
Star Schema from Single Flat File Source (Query Editor)
This file uses the query editor to split out a table of transactional sales data (based on Adventure Works) into a star schema – a sales fact table and product/date dimension tables.
Star Schema from Single Flat File (PBIX File and Excel data source)
This is demonstrated in the topic Combine or Select From Multiple Files.
Actuals vs Budget (Query Editor)
This example looks at how to combine transactional data at a different grain – we have actuals data at the granularity of Segment, Country, Date, Product and discount band; the budget data however is just at the granularity of Segment, Country and Month.
In the topic Combine or Select from Multiple Files we look at how to group the actuals data by month (in order to bring it to the same grain as the budget data), and how to create conformed dimensions in the scenario where we want to keep them as separate data tables.
Actuals vs Budget (PBIX and Data Source)
Selecting the Latest File in a Folder (Query Editor)
This report example looks at selecting the latest file in a folder based on a date suffix as part of the filename (e.g. “Daily File_20090920.xlsx”).
In the topic Combine or Select from Multiple Files we look at extracting the suffix into a date, and then choosing the file with the latest date
Dashboard In A Day (DIAD) Report
This is a report put together (and regularly updated) by the Power BI Product team for a standardised Introduction to Power BI course called Dashboard In A Day (DIAD). This course is delivered by Microsoft partners and training providers across the globe.
The latest version of the content can be downloaded at https://powerbi.microsoft.com/en-us/diad/
This report is used in LDI to highlight some concepts (e.g. in Manage Table Relationships to show creating a concatenated field for a relationship).
Want to provide feedback on this blog post or enquire about having training for your company/team? Fill in the form below and let us know!