Visualizing Retailer POS Data using PowerBI

Consumer Goods Companies strive to be more demand driven to improve revenue while reducing Out-Of-Stock issues. CG Companies rely on Demand Signal Repositories (DSR) to streamline the supply chain.  A DSR is a demand data warehouse of retailer and consumer demand activity, including demand, replenishment, merchandising, sales, and marketing. A DSR combines many varied sources of information, such as syndicated data, ERP shipments and budgets, retailer forecasts, trade promotions, demographics & POS data. 

I have been working with Retail Velocity (formerly known as Vendor Managed Technologies, Inc) and their DSR Solutions. Velocity manages the entire process of collecting and updating retail POS demand data, including EDI, AS2, and proprietary sources.

Power Query for Excel 2013 allows you to easily discover and connect to data from public and corporate data sources. This includes new data search capabilities to facilitate discovery, as well as capabilities to easily transform and merge data from multiple data sources, so you can analyze the data in Excel.

A sample dataset from Retail Velocity (with ~30,000 rows) is now available through Power Query in Excel and is an excellent way to better understand some of the data provided by Retail Velocity for DSR and gain insights from it using Power BI.

To test this out, I launched Excel 2013 and ran a search using Power Query:


Using the Online Search feature in Power Query, I search for velocity using the search bar:


Power Query Online search enables you to discover Public as well as Organizational data sources. You can use the Query Editor on the data to apply some easy transformations on the data before loading it into the data model in Excel:


I used the Query Editor to change the data types of the fields that I wanted to use in calculations to a Decimal Number. I also added a custom field combining the Address elements to create a FullAddress field that I could use for mapping the stores.


Using Power View for Excel 2013, I created a quick dashboard that gave me the insights on the performance of the Retailers, Brands, Stores and Products using YTD Sales data.


To get additional insights and correlations from the data, I used US Demographics data provided by Neustar. The data was acquired from a CSV file using Power Query:


The US Demographics data is organized by zip code and is great for connecting to the store data by Zip code. I used PowerPivot to establish a relationship between the Retail Velocity Stores Sales data and the Neustar US Demographics data.


Using Power Map for Excel 2013, I created a visualization that showed all of the store performance data as a map layer overlaid on Median Income data. In addition, I also created a layer of Brand performance across the country overlaid on the demographics data as heat maps. Here is a video tour of the visualization:

Try accessing the sample data and create your own dashboards and visualizations using PowerBI. Contact Retail Velocity for going beyond the samples into real data from Retailers.

Here is an interactive version of the PowerBI visualization:

Comments (0)

Skip to main content