Basket Analysis

I was doing some exercises one day and just want to share with you how you can use basket analysis to help your business to make better decisions.

I have some mock up data as below. What I would like to do is to figure out which item is sold with which item in a single order. A use case scenario with this, for example item A is normally sold with B, is you can place item A next to B to maximise your sales or offer a discount if customer buy A and B as it’s highly likely they will do so.


The trick to easily analyse this dataset in Tableau is that you have to slightly modify it as shown below. Drag the same excel sheet twice and join them.(Note if you can not use “<>”when you join the “Item ID” , try save your excel workbook as 1997-2003 version, which is exactly what happened to me when first did this using Excel 2016)


Once you have prepared your dataset like this you can quickly do what I did in below graph. Drag “Item ID” to Columns and another instance of “Item ID” to Rows, finally right click “Order ID” and drag it to “Text” and select CNTD (distinct count). If you don’t see CNTD, use data extract instead of live connection.


We can see that whenever people buy item B they also buy item A or C.  A more nice looking version of the graph is as below (I used famous superstore dataset which shipped with Tableau). I want to point out that the square at the intersection between “Computer Peripherals ” and “Paper” is the biggest so it indicates that it’s very likely that people buy this 2 items in a single order.


Hope you get the idea. Drop me a note if you have any questions.

Once again I want to point out that how quickly you can get some insights out of your data with modern tools like Tableau. Next post I will write something about Alteryx, best friend with Tableau and show you how you can use both tools to make your life even easier in some situations. Stay tuned.

Budget VS Sales

A common business case is to compare your sales number with budget. There are many blog posts about this already. See here for Rob Collie’s post on this topic.

Today I want to show you how you can do this easily with the function of adding calculated table in Power BI desktop. With this functionality you can add calculated table to your data model and treat it as normal table, add relationship to it and do your analysis as usual. In our case we will load some sample sales data (at day level), use calculated table to add a Sales by Month table, then compare this with budget data (also at month level). You can find the New Table button in the Data view ribbon under Modeling as shown below:

Calculated Table In Power BI Desktop

The model is very simple. I loaded sales data (at day level) and budget data (at month level). I added Sales by Month table with DAX function

VALUES ( ‘Calendar'[YearMonth] ),
“Sales”, CALCULATE ( SUM ( Orders[Sales] ) )
[Sales] > 0

I added calendar table with new DAX function CALENDARAUTO(). What it does is based on the dates in your data model, it will add one column table with dates from min dates to max dates in your data model. Very cool and handy. When you have new sales data coming in, your calendar table will update too. Neat! You can see the model in the below picture.Budget VS Sales Model

Lastly I created some simple VIZ to compare sales and budget as show below. Power BI desktop is still evolving and Microsoft is releasing new updates on a monthly basis. As time goes by I believe it will offer the same charting power as Tableau.

Budget VS Sales VIZ

You can download my sample file here for closer look what I have done.

Tableau + Power BI

Tableau and Power BI are both great BI tools and you normally get to play with one of them as no company will spend money to get both. But for those of you who use Tableau and have Excel 2010, you should consider bringing some of family members of Power BI into your set of tools. Let me tell you why.

First of all, Power Query and Power Pivot are both free add-ins for Excel 2010 and they are the best gifts from Microsoft, these is no reason not to install them. Secondly, If you use Tableau for reporting, you know Tableau likes flat file and you will try everything to create the flat file. Creating a Tableau extract or connecting live to a database and use SQL joins to bring multiple tables together are the ways you can do just that. But what happens if the data you need reside in different database? Ask your IT to link one as linked server so you can join tables from these 2 database to create a flat file or ask your IT to do it for you? When you work for a big organisation, that’s something will take long time to happen or not at all. So what do you do then? As data geek we never give up, Power BI comes to the rescue. As seen below graph, I successfully used Power Query ( one member of Power BI family which is made up of PowerPivot, Power Query, Power View/Power Map) to create one data extract using data from 2 different databases and 1 Excel file.


What happens next is you could suck the new data file into Tableau and done with it or you can load the data file into Power Pivot and then use Tableau to connect to your Power Pivot to visualise the data. As we know Tableau’s visualisation capability is way better than Power BI at the moment. But It’s much easier to get the numbers using Power Pivot. Like I said in my first post, getting the right number and how easy to get them is more critical. Since you have both tools at your disposal, why not get the most out of them. I have take the second approach. PowerPivot’s DAX formula and time intelligence functions and doing fantastic jobs to get the numbers I need, then I use Tableau for visualisation. See below cool tree map generated by Tableau. (the bigger the square, the higher the sales value).

Let’s applause for the real power brought to you by the combination of both tools. Happy analysing!


My first post

Hello world, I have always wanted to start my blog as I would like to share what I know about Self service BI, more specifically, Power BI and Tableau. I do know a little bit about SQL server BI stack which comprise of SSIS, SSAS and SSRS, but  I do not know them well and enough to share.

Hence this is my first post, I think I should introduce myself. My name is Bob and I have been working as a BI Reporting Analyst in Financial sector in Sydney Australia. My career as a BI Reporting Analyst started with designing report in Power Pivot which I started using back in 2010. I have to admit that part of the reason why I liked Power Pivot so much back then is because Rob Collie’s blog I have been following his blog almost since he started blogging on his site. Since then his site leads to a couple of other blogs as well, such as Chris Webb’s and SQLBI. I have learned a lot from these people and I have bought every book written by these people on Power Pivot and Power Query and I would highly recommend those blogs and books if you are serious about Power BI. Nearly half year ago I started using Tableau, I have to say that it’s a fantastic tool for visualisation, and Power BI lags a lot in this space. As Rob mentioned in one of his blog post that there are 2 kinds of people in the world: people love numbers and people love visual. Like Rob I am in the first category and how easy it is to get the numbers and get them right is far more important than visualise them. Power Pivot does a freaky fantastic job in this, hence if you ask me to choose, I will go for Power Pivot without any hesitation.

I better keep my first post short and hopefully I will share something useful to you in my next post. Thanks for reading.