Skip to content

Compelling Data Storytelling with Power BI: Global Brand Insights - 20 Years of Financial Trends

Barry Smart Barry Smart

Power BI Data Storytelling

Explore the creative journey behind the Global Brand Insights Report on Power BI, showcased on the Power BI Data Stories Gallery.

We crafted this report for educational purposes to highlight features in Power BI that allow you to work with data rich in imagery. In this case, we wanted to show how brand logos can be adopted in a Power BI report to improve accessibility and the drive an engaging user experience.

In the report we work with financial data which has been sourced from Yahoo Finance. We explore visuals that work well with financial data that spans a time period of 20 years. This is aided by a custom date dimension which is used to aggregate financial results from the raw daily level, up to weekly, monthly, quarterly and annual levels.

We explore the use of a 2D heat map to display high density information, showing how a RAG (read, amber, green) colour scheme can help your eye to isolate trends and anomalies in the data.

We dynamically rank each company based on its share price in any year. This brings out some interesting techniques based on use of rank to explore relative performance of the brands over time. We adapt the Formula 1 lap chart visual for this purpose.

We support the end to end user journey using a custom icon based navigation, custom tool tip and drill through.

Chapters

  • 00:00 Introduction to Endjin's Global Brand Insights Report
  • 01:25 Data Sourcing and Preparation
  • 02:40 Data Wrangling and Aggregation
  • 04:45 Building the Power BI Report
  • 05:44 Creating Interactive Visuals with DAX Measures
  • 08:11 Exploring the Report Pages
  • 10:48 Analyzing Trends and Rankings
  • 13:25 Conclusion and Key Takeaways

From Descriptive to Predictive Analytics with Microsoft Fabric:

Transcript

Hello and welcome to this tour of endjin's Global Brand Insights Report which generates insights for 25 global brands using 20 years of financial data.

We crafted this report for educational purposes to highlight features in Power BI that allow you to work with data rich in compelling imagery.

Now in this example, we have chosen to work with global brands that have logos that are widely recognised by everyone. We wanted to show how you can use these logos in a Power BI report to improve accessibility and the overall end user experience.

We picked 25 brands, across five categories of:

  • Technology
  • Food and drink
  • Entertainment
  • Finance; and
  • Automotive

Each of those categories in their own right invoke imagery, which we've used in the report, you'll see that later.

We then sourced 20 years of financial data, at daily granualrity, for each of these companies to give us something to work with in the report. We've loaded this into Power BI and then generated descriptive analytics over the data and to start to generate data stories.

Before we show you the report, let's go into more detail about how we sourced and prepared the data.

To source the data we used the "YFinance" Python package. This enables you to connect to and download data from the API provided by Yahoo Finance into a Pandas dataframe.

With this package it was really easy to download share price data and share volume traded data at daily granularity for each company, so it met our needs perfectly.

We developed and ran the code in Visual Studio Code, which we love. We used a desktop machine, and exported the final data into a set of local CSV files.

Now we could take this simple approach, because this is not a production solution and we are dealing with a relatively small amount of data: just over 100,000 records in total. So we were not IO or CPU constrained in this case.

However, if we did need to productionize or scale up this process, we could import the Jupyter notebook into Microsoft Fabric and tweak the code so we were writing the data to One Lake.

Now we haven't done that here. Perhaps, if you are interested, we can show you that process in a future video?

So in VS Code we imported the YFinance package into a Jupyter notebook which was used to orchestrate the end to end task of downloading and wrangling the data ready for importing it into Power BI.

Given this data comes from a mature API, it is nice and clean. So we didn't have do too much work to the data. There were four key things that we did in the notebook:

  • Firstly we prepared the logos and icons that we wanted to use in the report. We staged those on Azure on public facing blog storage so that the Power BI Service could access those images. My colleague Paul, who is our designer at endjin, took a lot of care here to source or create SVGs or vector graphics. That meant that whatever size we chose to use the logos in Power BI, we knew that they would retain image quality because these are vector based graphics. So that's a key tip for you if you are going down this route.
  • The other task chose to do was to aggregate the data. We had to make multiple calls to the Yahoo Finance API, one for each company, so we had to aggregate the data up into one data set.
  • Another task we've done in the notebook is to normalise the closing price data for the share prices. We realised because for each company we are working with different ranges of share price and different currencies, by normalising the share price in this way, between 0 for the lowest value and 1 for the highest value, we were able to create a level playing field and to support the like for like comparison we wanted to do between the different companies in the report.
  • And then the final thing we've done in the notebook is to build a date dimension table, and this defines the rules that we wanted to apply to aggregate the data from raw daily level, to weekly, to monthly and so on. This plays an important role in the report, and will see that later on.

Having created a final set of CSV files, it was a case of importing these into Power BI, that's a somple process it takes a few seconds to do that.

Because we pushed all of the data wrangling upstream into the Jupyter notebook there was very little to do in Power Query so very quickly we could start working with the data in Power BI.

The first step was to create the relationships between tables to construct a dimensional data model in Power BI with:

  • the financial data in the core fact table
  • a date dimension table, which we build in the notebook; and
  • a company dimension which uses a snowflake arrangement to group companies into one of the five categories that I talked about earlier.

This star schema arrangement provides the foundation for the interactive report. It's on top of that star schema that we also build our DAX measures, and we added a few of those.

DAX measures are formulas that are written in Power BI. They are calculated dynamically based on the context in which they are being applied.

In other words as we build the report pages, and thereafter as users interact with the visuals through actions such as filtering or drilling into the data, these DAX measures are re-calculated based on that changing context in which they are being applied and that brings the report to life.

The most complex DAX measure we've created allows us to rank the companies against each other based on their normalised share price in any year. Then we've layered on top od that to calculate the change in rank from previous year to current year.

We've tested these measures informally using the new "DAX Query View" in Power BI.

This allows us to inspect, in a table, the raw data alongside the output from the DAX measures, allowing us to check that the DAX measures are behaving as expected.

If this was a production workload, we would formalise these tests, running them externally as part of the continuous integration / continuous deployment process.

One final thing we've done to complete the semantic model is to classify the columns which contain URLs that point to the logos and icons we've staged on blob storage as "Image URLs".

This tells Power BI to render these URLs as images when we use them in the report.

We ran some experiments to find out which of the visuals provided "out of the box" by Power BI support rendering these "Image URLs". We found that only 5 out of the 33 visuals provided with Power BI support this functionality.

The biggest gap seems to be with the traditional bar, column, line and area charts where it would make sense to render an image URL column if you place it on the categorical axis. It would be great if Microsoft could add support for this! But there are plenty of visuals that do support Image URLs, so that's what we've tried to focus on in the report.

Then it's all about building the report pages that allow us to create that user journey that allows users to explore the data and to create data stories.

First we have the cover page, from here we are directed to the introduction page which provides the background and context for the report. From here we can start to navigate around the different pages using the navigation icons on the left. This is our first use of imagery to build that user journey through the report.

Firstly, the Share Price History screen - there is a lot of information to consume. But you can start to pick out some global trends, such as the impact of the financial crisis in 2008 and more recently the invasion of Ukraine in 2021.

The logos and icons on this page use the new filter visual to allow you to filter the data.

For example, let's filter to the companies in the tech category.

Here we can clearly see the recent exponential rise of Nvidia, it's share price has been boosted by the demand for its GPU technology which provides the infrastructure on which many AI models are trained and then run.

Moving onto market capitalisation, we live in the digital age, so perhaps it's no surprise that the big tech companies dwarf all of the other brands we've chosen to include in our report.

We use a custom tooltip here to surface more information about each company, including its logo, as we hover over the bars on the chart.

Next, moving onto the location page. This allows us to visualise where these brands have their headquarters around the world. You can see they are spread relatively evenly spread. But there are also some notable gaps in areas such as South America, Africa and Oceana.

The company listing is a chance to show off Power BI's table visual.

Here we really go to town with visual elements: we've got logos surfaced using our image URL, we use icons to show the change in rank from last year to this year, we also use icons also to show the risk rating of each company.

We've also got conditional formatting with data bars to illustrate the magnitude of revenue growth seen by the individual companies. This highlights that Nvidia's growth has overshadowed all of the other companies.

Finally we also use the new "spark line" feature in the table visual to summarise the share price trend for each company, with dots to highlight the lowest and and the highest points.

Moving onto the category trends page, we show how monochrome pages can really pop visually. By omitting the use of colour, and instead using shapes to highlight different patterns, the page feels easier on the eye.

We use the line and marker styles instead of colour to separate the different categories on the chart, which are clearly identified with series labels and a legent. It's a great example of how "less can be more" when it comes to choice of palette.

Moving onto the ranking page. Here we've stolen an idea for Formula 1. Formula 1 uses this visual as a lap chart to show the relative position of each driver lap after lap during the race. Here we have used this to show the relative ranking of each company in each year.

This is an interesting way of observing relative performance - we have picked out 2 financial services companies, Lloyds and Visa. You can clearly see the contrasting performance over the last 20 years in terms of how their share price has performed.

An alternative means of showing the same data is a heat map. Here we have created a heat map using Power BI's highly flexible Matrix visual. We've used years as columns, companies as rows and the average normalised share price as the values.

We've layered on top of that some conditional formatting that highlights low percentages in red, mid percentages in yellow and high percentages in green. This use of red, amber, green (or RAG) is a very common way of flagging good versus bad in reports.

It's quite hard on the eye, there is a lot of colours. But you can see, in general, the performance of most companies is improving from left to right. You can also easily spot the exceptions to this trend and focus in on those.

Finally we have developed a drill through that allows you to explore a specific company in more detail. Let's look at Lloyds for example. You can see we have applied our "monochromatic" design philosophy on this page.

The main chart shows share price and volume of shares traded, and buttons allowing you to focus on different time frames where we choose appropriate granularities for those time frames as you move between them.

So we hope this report has given you some ideas about the features in Power BI that are suited to analysis of brands, consuming financial data, and analysing relative performance:

  • We've used imagery throughout the report - company logos, icons and pictograms - to support the user journey and build those data stories throughout the report.
  • We've used DAX measures to enhance the report - in this case to "rank" the companies, allowing us to uncover some interesting insights about relative performance.
  • We've stolen some ideas from other domains. In this case, we took inspiration from Formula 1, adapting their lap chart visual into this domain. This technique can really help to engage people. In this case, it would be getting the motorsports enthusiasts in the room sitting up and paying attention!
  • We've also chosen a colour palette that works. Firstly, we showed how monochrome pages can look really clean and allow you to cut through the noise. We also showed, at the other end of the spectrum on our 2D “heat map”, how we can adopt RAG (red, amber, green) colour palette to visualize high density information and bring out trends in data.

Underpinning all of this is the principle of dimensional modelling, one specific feature in our star schema was the bespoke date dimension that we crafted to allow us to aggregate from daily to weekly, to monthly, to quarterly to yearly levels of granularity, enabling users to interactively drill up or down through that hierarchy.

We also showed off some of the latest features in Power BI (which is constantly evolving) such as spark lines in table visuals which are a really powerful way of summarising financial trends.

I hope that's been useful demonstration of how a solid semantic model and strong visual design can help bring that data to life and enable you to tell data stories.

If you've got any questions or any thoughts or have any feedback, we'd love to hear from you.

Check out our other examples in the Power BI data stories gallery. Please like and subscribe to our YouTube channeel!

Thanks very much for watching. Bye bye.