Skip to main content

Change over Time Analysis in Power BI

In an earlier post I showed you a Power BI interface for summarizing Georgia export data from the US Census Bureau. If your primary interest is year totals, that model is useful for understanding our State's export economy. 

But there are some questions that particular Power BI model does not answer. In particular, it does not address change over time questions like: 

  • What product categories have the greatest gain or loss?
  • What countries were responsible for those gains and losses?

This blog article and an accompanying video explain how the updated version of our Power BI model address these questions. The video takes you step-by-step through the system. The paragraphs and images below are a quick orientation for you. Note: clicking the images will increase their size.

Now -- let's get started!

Net change in Total exports, 2013 vs 2017

Think of the image at right as an explanation of what was different about Georgia Exports in 2013 versus 2017. As you exercise this data model, remember that:

  • The left pane shows the product hierarchy with totals for each period. 
  • The right pane is the distribution of those exports by the country that purchased goods from Georgia.
  • Whatever you have selected on the left side of the screen is shown in more detail on the right side. 

Because we haven't selected any individual category on the left pane, this image displays our State's total export values. What can we glean from this report?

Viewed at bottom left, it shows a $354 million net loss going from 2013 to 2017. Looking on the bottom-right side of this image you'll notice net declines in trade between 2013 and 2017. These are led by China ($892 M), Brazil ($314M) and the UK ($238M). For the positive side look to the top-right of image. It shows that Mexico ($1,024M), Germany ($757M), India ($528M) and the Netherlands ($279M) increased their combined purchase of exports by over $2.5 billion from 2013-2017.

Net Change in Textile Exports, 2013 vs 2017

When looking at the left side of the first screenshot, it shows that the Textiles category declined by $422 million or 18.7%. By selecting the Textiles category on the left pane, we learn what countries contributed to the decline in trade comparing 2013 with 2017. The result can be seen in this second screenshot. 

An alternative view of the Textiles category is provided on the third screenshot composed of horizontal blue bars. It is sorted in descending order by the purchased amounts in period 1 (2013). It reveals that Canada leads in purchasing textiles and that both Canada and China have reduced their purchases over this time period. 

The video link below demonstrates this revised Power BI model. 

After watching perhaps you'll agree - Power BI is uniquely suited for evaluating this Census export data.

Addendum: Tweaking Display Parameters 

In the video, I made a passing reference to two parameters that control the granularity of detail on countries. In case you are curious, here is a more detailed explanation of how these parameters affect the right side of this page (i.e., the display by country.)

For a country to appear on the right side, they must exceed both the minimum Monthly Difference and the Minimum Market Share parameter values. Each measure is described further below:

  • Minimum Monthly Difference: What is the minimum monthly change in dollars between the two periods that a country must experience to be included? Typical value is $1 million. This effectively hides countries who did not purchase a significant amount or that have stayed the same in their purchasing behaviour over the two periods. 
  • Minimum Market share: What portion of the total market share must a country have in order to be listed? Typical value is ".01". The purpose of this parameter is to hide the "noise" of countries who may have wide fluctuations in their own purchase history, but that in the overall scope of the market share are not significant for Georgia. In evaluating this parameter, the Power BI model evaluates both periods in question. If in either period a country exceeds the minimum market share, it will be included in reports on the right side. 

Please let us know if we can help you make use of these Microsoft data tools in your organization.