Category: PowerBI desktop

Here's a simple Sales data of a retail Store which sells Apple Products.  Since a customer can transact multiple times, there will be repetitions in the Cust ID column.  While Cust ID 123 and 782 purchased multiple products from the same Store in one transaction, Cust ID 53 purchased multiple products from different stores (Store ID 165 and 45) [and therefore the Order ID's are also different (Order ID 2 and 6)].

On this small sample, one may want to identify "Cross and up selling opportunities" i.e. one may want to know which are the Customers that can be approached for selling more products to.  So for e.g. one may want to know which Customers have bought only one product so far.  A case in point being the Apple Watch - Customer ID 2442 and 428 bought only this product.  The other Customers who bought the Apple Watch also bought atleast one more product.  Therefore, Cust ID 2442 and 428 could be approached for buying other products as well.

Solving this via conventional Excel formulas and Pivot Tables would prove to be a formidable challenge.  I have solved this problem using a Data visualisation software from Microsoft called PowerBI desktop (it can be downloaded free from the Microsoft website).  This problem can also be solved in MS Excel using Power Query and Power Pivot.

There are 3 sections in the image below - Table at the top (First Table), slicers at the right and another Table at the bottom (Second Table).

Interpretation of First Table

1. 1 appearing at the intersection of APPLE TV (row labels) and APPLE TV (column labels) represents that there is 1 customer who bought the APPLE TV
1. 1 appeaing at the intersection of APPLE TV (row labels) and MACBOOK AIR (column labels) represents that the 1 customer who bought the APPLE TV also bought the MACBOOK AIR
2. 4 appearing at the intersection of APPLE WATCH (row labels) and APPLE WATCH (column labels) represents that there are 4 customers who bought the APPLE WATCH
1. 1 appearing in other columns of the same row represents other products which those customers bought
2. When one right click's on APPLE WATCH and selects "Drill down", one will be able to see the Customers who bought the other products as well.
1. Customer 53 bought the APPLE WATCH, AIRPORT and IPHONE 8S.  Customer 123 bought APPLE WATCH, IPHONE X and IPOD
2. Customers 2442 and 428 did not buy any other product

Interpretation of Second Table

This table shows a list of Customers (and their transaction details) who bought only and only that one product selected by the user in the filter section (see the red oval selection in the image).  So these two customers could be approached for selling more products to.

Refer to this simple Sales dataset

The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear till the month for which there is data for the current year.  For e.g., for 2018, data is only till Month 4 and therefore for prior years as well, data should only appear till Month 4.  As and when Sales data gets added below row 17, data for prior years should also go up to that month.

The expected result is

You may download my PBI file from here. The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

This table contains a list of all the inspections created and completed within different time periods.

The objective is to create two Tables from this single table - one showing the Accounts created within the chosen time period and another showing the those that were closed within the same time period.  Here are screenshots of the expected results.

You may download my PowerBI desktop solution workbook from here.  The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

This post is in continuation of an earlier post where I applied Excel's Business Intelligence tools (PowerPivot, Power Query and PowerView) to analyse the Sales data of an E-Commerce Company.  So, just for starters, in that post, I have basically sliced and diced the Sales dataset of an E-Commerce Company from multiple perspectives/facets to know the performance of this Company by Categories, Products, SKU's, Shipping Cities, Modes of payment etc.

After analysing the data, I have also visualised that data using PowerView.  From the link shared above, you may download the workbook, watch the YouTube video and see a PowerBI desktop custom visual ("Sankey Diagram"). In this post, I have taken the same dataset and showcased/discussed the following:

1. How one can discover insights from this data with minimal effort using a Custom PowerBI desktop visual called "Sand Dance"; and
2. How one can query the dataset using "Natural Language" on a web browser (using www.powerbi.com); and
3. How one can query the dataset using "Natural Language" using Cortana (Microsoft's personal digital assistant in Windows 10).

For aspects 2 and 3 above, here are a few "Natural Language queries" which returned the correct result:

1. Show total revenue and growth in total revenue over previous month where order status is delivered by month in ascending order of month order as a Table
2. Show total revenue by category as a column chart
3. Show total revenue by order period as a pie chart in descending order of total revenue
4. Show total revenue by order period as a pie chart in descending order of total revenue where day of week is Sunday
5. Show Business generated from new categories by month where order period is mid day, payment type is COD sorted by month order in ascending order as a table
6. Show total revenue where portion of month is first half of month

Enough talking!!.  You may view all three aspects mentioned above in this YouTube video

You may download the Powerbi desktop workbook from here and play around with the Sand Dance visual yourself. The PowerBI.com service also allows one to Publish reports to the Web (which can be viewed and interacted with by anyone).  This is currently in preview stage and may become a payable service later.  You may view and interact with the Sand Dance visual here:

Assume a 4 column dataset (a small sample) as follows:

 City of Origin City of destination Mode of Transport Passengers travelled New Delhi Pune Air 123 New Delhi Mumbai Air 213 New Delhi Kolkata Air 125 Chandigarh Jammu Bus 785 Chandigarh Amritsar Train 567

Given this dataset, one may want answers to the following questions:

1. Of all those passengers who originated their journey (City of Origin) from Chandigarh, how many terminated their journey (City of destination) in New Delhi via different modes of transport; and
2. Of all those passengers who terminated their journey (City of destination) in Jammu, how many arrived in Amritsar (City of Origin) via different modes of transport; and
3. Of all those passengers who travelled by Bus, how many travelled from City A (City of Origin) to City X,Y,Z (City of destination)

While one can analyse/slice and dice this data using Pivot Tables, one cannot visualize this data very clearly (even after creating a Pivot chart).  I have attempted to visualize this data using a software called PowerBI desktop (a free for download and use Business Intelligence software from Microsoft which rolls all of Excel's BI tools into 1 - PowerPivot, Power Query, Power Map and Power View).