Microsoft Custom Visual – Bullet Chart Can’t do exactly what I want

I have recently came across a problem like this. I would like to visually present some data shown in below table in a graph like the one below my bonus table where I can easily see what my bonus% value will be base on my sales number. In below bullet chart I can easily see that I will get 2.50% as my sales 499K falls in the fourth tier.

Data tier
Bonus Table

bullet

I have had a look at all available Power BI custom visuals at that time and thought that the bullet chart from Microsoft might be just what I need, so I gave it a short. After nearly an hour of playing around and trying different formatting options, I can’t seem to get what I want exactly.

bullet chart

Microsoft Bullet Chart

The problems lies in the chart itself. With this chart I can only have 5 color for my bonus% tier and I need 7. Note the last one -black color is for the bullet. So you have 5 color not 6. Also you don’t have control over your X-axis label, nor do you have control over the size of it. You cannot even make the bar wider. Anyway it can’t do exactly what I just needed.

Capture2

Do I just give up and stop here? That’s a big NO for a data geek like me. So I tried below.I created a picture like below and you can do the same in any tools of your choice. I created mine in Excel. As you can see it already has the labels and color tier I need.

Capture

Insert this as a image onto you power bi canvas and then visualise your sales number in a normal bar chart. Finally move your bar chart on top of your inserted image, then you will see the bullet chart I showed your in the beginning of this post. If you don’t get what I showed you before, use below button to send your image to the back.

send to back

Finally, the normal bar chart let you fix your X-Axis label, that’s exactly what I need. Set end value at 2M, my bar chart will always be in proportion to the image underneath.

Capture3

There you go. Hope this post can give you some idea of how thinking out of box can sometimes saves your day. If you know other ways or maybe better ways to do what I wanted, please share with me as I would like to learn from you as well.

Last but not least, I hope Microsoft can enhance it’s bullet chart custom visual, so it can better serve our needs.

Advertisements

Alteryx hand in hand with Tableau

I have covered similar problem using Power BI before, you can find it here. Now I would like to show you how you can tackle this different granularity problem with Alteryx. Alteryx is an powerful ETL tool but it also does other amazing things such as spatial analysis and predictive analysis. I will cover these in future post. so what I am showing you today is how you can use Alteryx to prepare your data and spit out a tde file for tableau to visualise your data. (note that Alteryx has basic reporting capability as well but it’s horrible compared to that of Tableau). Some sample data as below: daily level transaction data and quarterly sales quota data.

1
Daily sale data
2
Quarterly sales quota data

If you load these data into tableau it’s not going to be able to consume it easily. You can ask your IT to break your quarterly sales quota data down to day level and then use tableau to visualise it. Since we are in the era of self service BI, let’s do it all by ourselves with Alteryx. I have massaged the above data set to be in below format so that both data set are now at quarterly level. Now I can easily join them and find out which sales person hits target for each quarter.

I designed below workflow in Alteryx to do the job. (I could have used SQL script to get desired data for my analysis, but I want to show you how you can do it with Alteryx)

3

Step 1: drag an input tool to get my sales data from my local instance of SQL server. You can see the incoming data and data type as below. Note that SalesAmount is string type.

Step 2: drag an auto field tool to change the data type. You can see SalesAmount is now Double type.

Step 3: drag a datatime tool to parse the OrderDate to remove the time component.

10Step 4: Drag a text to column tool to split the DateTime_Out field.

12.PNG

Step 5: drag a formula tool to get my Year-Quarter column.

1314

Step 6: Drag a select tool to remove unwanted column.

15.PNG

Step 7: Drag a summarise tool to aggregate my sales data to quarter level for each sales person. I have put step 1 to 7 in a container so it’s easier for other people to follow my logic flow.

Step 8 to 11: Manipulated the sales quota data set coming from SQL server to be in the same structure as my sales data. You can see the end result as below.18

Step 12: drag a join tool to combine the 2 data sets.19.PNG

Step 13: I have added 3 browse tools to check my output data set from step 12. This tool is often used to see your data set after transformation performed to ensure you get intended result. I don’t expect to see any data in the upper and bottom browse tool as all my employees should have sales quota defined in my data set. If I had employees without associated sales quota or vice versa, then I would have seen data coming out of those 2 browse tools.

Step 14: drag a output tool to generate a tableau tde file, so you can visualise your final output in Tableau. My mock-up data set for sales quota is too high as no sales person had reached it. Anyway, you get the idea how you can use Alteryx and Tableau together for your analysis.

20.PNG

 

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.

1

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)

2.PNG

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.

3.PNG

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.

4.PNG

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

=
FILTER (
ADDCOLUMNS (
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.

3

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!

1