Back in the old days I remember I had used Excel REPT function to hold a bar in a cell like the graph below (I can’t recall why I did this), but I am sure there is some great use of this trick out there. You can see the formula in the formula bar. You also need to set the font to Britannic Bold for this to work.
Good news is now you can use the same trick in Power BI as well . Chris Webb has recently blogged about this. Go check it out.
Microsoft released Power BI April update a few days ago. I am very excited about 2 new features -quick measures and add column by example. These 2 new features are great for Power BI beginners because now you can add measures and custom columns without needing to know DAX and Power Query M language. Power BI will automatically create them for you. It’s also a great way of learning DAX and M by looking at the code generated by Power BI.
You can create a quick measure by selecting Quick measures from the field menu, either in the field of a chart or from the field list.
In the dialog, you can pick any of the 19 different calculations to perform on the selected measure. Depending on the calculation, you’ll have different required parameters you’ll need to fill in. You can also change the fields being used in the calculation using the field list on the right half of the dialog. Click below link for more detailed info on this feature.
The add column by example feature is really like Flash Fill introduced in Excel 2013. It works like by manually typing some sample values you want then Power BI will apply the same transformation rule to other rows of the same column. The more sample values you type the more accurate it will get. Watch below video for a complete run down of how it’s doing the magic.
P.S. Quick measure is a preview feature so you need to go to option menu to enable it
Click File menu in your ribbon and choose Options and Settings, click on Options to open Options dialog, then click Preview features to enable it
If you have been using Power BI and think about how you can host your report locally, good news is you will be able to do so very soon. Microsoft released technical preview for on premise hosting of power BI reports inside SSRS. Below link is the official guide from Microsoft on how you can actually do this. I won’t repeat here on the how’s, but I would like to call out a few things that might still confuse you.
1 It’s still beta version and the only supported data source for your Power BI reports at the moment is SQL server analysis service and you have to use direct query. That’s right, live connection to your SSAS cube (either multi dimensional or tabular) instead of loading data from cube to your Power BI data model. Microsoft is planning to add more data source in the future.
2 You may wonder why live connection? Because to make SSRS integrate with Power Query seamlessly is not an easy thing and Microsoft needs time to deliver this. So at the moment, only live connection to your cube.
3 Microsoft is targeting mid 2017 to release the GA and the licensing details won’t be available until close to that time. so sit tight and it’s coming.
Now without further ado, go try out the preview feature on your local server or VM.
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.
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.
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.
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.
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.
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.
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.
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:
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
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.
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.
You can download my sample file here for closer look what I have done.