Tag Archives: MSBI

Boston BI User Group Meeting–Dashboard Design with Microsoft: Power BI vs Datazen (10/13/15)

Boston BI User Group

Thanks for joining Anthony Martin (@SQLMartini) and I at the Boston BI User Group Meeting in October. During the session, we demo’d and built dashboards in Power BI Desktop and Datazen Publisher.

Power BI



Couple of thoughts from our demo:

  • Power BI is a way to get data, model data, and visualize it
  • Power BI Desktop allows you to work with data on your PC
  • Power BI is experiencing a lot of change – for example over 40 changes were applied in September 2015
  • Power BI has an open API that allows you to customize the experience




Couple of thoughts from our demo:

  • Design first scenario – make it look good, then shape data to match
  • Datazen publisher allows us to create dashboard for many different profiles
  • Datazen handles custom shapes

Additional Training from Pragmatic Works

Questions from the Session

Can you use links in Datazen to support drillthrough?

Yes. You can find more information here: Drill-throughs to Other Dashboards or Custom URLs.

Power BI API Development

You have the ability customize Power BI. Check out the contest winners to get some ideas of what is possible.


Details about the solutions can be found on the Power BI blog: http://community.powerbi.com/t5/Best-Visual-Contest/con-p/best_visual_contest/tab/entries.

You can find more about custom visuals here: https://powerbi.microsoft.com/en-us/custom-visuals.

Thanks again for joining us.

Excel Tip #29: Forcing Slicers to Filter Each Other when Using CUBE Functions

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!


You have went to all the trouble to build out a good set of slicers which allow you to “drill” down to details based on selections. In my example, I have created a revenue distribution table using cube formulas such as:

=CUBEVALUE(“ThisWorkbookDataModel”,$B6, Slicer_Date, Slicer_RestaurantName, Slicer_Seat_Number, Slicer_TableNumber)


Each cell with data references all the slicers. When working with pivot tables or pivot charts, the slicers will hide values that have no matching reference. However, since we are using cube formulas the slicers have no ability to cross reference. For example, when I select a date and a table, I expect to see my seat list reduce in size, but it does not. All of my slicers are set up to hide options when data is available. There are two examples below. In the first, you can see that the seats are not filtered. However, this may be expected. In the second example, we filter a seat which should cause the tables to hide values and it does not work as expected either.



As you can see in the second example, we are able to select a seat that is either not related to the selected table or has no data on that date. Neither of these scenarios is user friendly and does not direct our users to see where the data matches.

Solving the Problem with a “Hidden” Pivot Table

To solve this issue, we are going to use a hidden pivot table. In most cases we would add this to a separate worksheet and then hide the sheet from the users. For sake of our example, I am going to put the pivot table in plain sight for the examples.

Step 1: Add a Pivot Table with the Same Connection as the Slicers

In order for this to work, you need to add a pivot table using the same connection you used with the slicers. The value you use in the pivot table, should only be “empty” or have no matches when that is the expected result. You want to make sure that you do not unintentionally filter out slicers when data exists. In my example, I will use the Total Ticket Amount as the value. That will cover my scenario. In most cases, I recommend looking for a count type value that will always have data if there is a potential match of any kind.


Step 2: Connect the Slicers to the Pivot Table

Using the Apply Filters button on the Pivot Table ribbon, you need to select all the slicers you want to interact with each other.


Once these changes are applied, you will see how my data changed.


Now, let’s test this for real. We will keep the date and table, but now we will see that the other slicers are now filtered to match the data that is available.


As you can see, the solution is fairly simple, but not intuitive. You will be able to create more creative dashboards with this technique. Keep in mind this issue is primarily a problem when using cube formulas in your Excel dashboard.

Until next time…

Excel Tip #25: Overlaying Charts

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Making a cooler Excel chart by overlaying charts

This is one of the coolest techniques when creating compelling chart visualizations in Excel. The concept is to overlay one chart on another such as line chart over a bar chart which is our example. This is really helpful when showing data that has very different granularity or scale. In the example we will do below, we are going to show the number of tickets each hour of a day with the % spending on alcohol for that time period. Let’s get started.

Step 1 – Create the bar chart

We are creating a bar chart that shows the number of tickets created during each hour of the day at my fictional restaurant – Sensational Servings. In my bar chart, you can see that I added a title, removed the legend, and hid the field buttons. Here is the resulting chart.


Step 2 – Create the line chart

Next, we create a line chart that shows what % of the total ticket amount for that hour of the day was on alcoholic beverages. I will not add a title to this one and I will hide the buttons and the legend as well. I will also convert this to a line chart so we can see the trend through the day as a line.


Step 3 – Set up the line chart to be overlaid on the bar chart

In this step, we will walk through the settings that make line chart ready to overlay. In my scenario, you may have already noticed that the list of hours are different on the Y-axis. In order to overlay the charts, they must represent space that can be overlaid. In this example, we need to make sure that the Y-axis on both charts match. We expect that the X-axis will be different as we have a count and a percentage we want to compare. In most cases you need to have one of the axis represent the same values so you can compare the other. However, you may have a situation where this does not matter. As we move through the rest of the example, you can adjust to your data visualization needs accordingly.

In order to set the hours the same, we will show empty values for the Hour of the Day. (NOTE: you may need to do this with both charts to prevent unexpected results.) You can make this change in the PivotChart Options dialog on the Display tab.


Here is the result:


Here are the other changes we will make:

  • Make the background transparent
  • Remove the Y-axis label
  • Move the X-axis label to the right side
  • Remove the lines in the chart

Let’s do the labels first. To remove and move the respective labels, you can use the cross that is displayed on the upper left corner of the chart. image  The first change is unselecting the Primary Horizontal axis from the Axes menu. From here you click the Axis option and then go to More Options. That will open a side panel with more options from which you can make the necessary changes. On this panel, you need to open the Format Axis page. You will see an option for Labels near the bottom. To move the X-axis to the right side you need to select Low or High. This will depend on the data in your axis. The change is applied immediately so you should see the change. In the example we need to change it to High.


While leaving the format panel open, select the one of the gridlines. This will open the Format Major Gridlines menu. Select the No Line option and these will be removed. Once you have completed that, select the entire chart and set the fill to No Fill in the Format Chart Area and select No Line for the border in the same section.

As with many Microsoft products there are other ways such as ribbon commands to change fill and line options. You should explore other options to see which method is the most intuitive for you. However, the format panel has the most complete set of options which are grouped for the section of the chart you are modifying which is why I used that for most of the changes here.

Once all these formatting changes have been made you should see the Excel gridlines behind your line chart and it should look as follows:


Now the line chart is ready to be overlaid on our bar chart.

Step 4 – Overlaying the charts and finishing touches

The final step is both the simplest and most annoying. You will need to drag the line chart on to the bar chart. Making no changes here is what my initial attempt looks like.


You will notice that the axis labels from the line graph are outside the bar chart border, the line is not sized to be meaningful and the points on the line chart are not matching the Y-axis labels on the underlying chart.

Here are the steps I took to get this chart the way I wanted.

  1. Made the bar chart wider. I did this to get the space I wanted to include the labels in the chart.
  2. Made the plot area on the bar chart smaller. This opened up white space on the right side for the line chart labels
  3. Next, I made the line chart shorter. Keep in mind that without a chart title the chart expands to fill. By reducing the height of the line chart it will fit in with the title and the labels will fit into the plot height of the bar chart.
  4. This step is the most painful. I lined up the points with the bars and made sure that 0% was lined up with the bottom of the bar chart. This takes patience to get it looking right.
  5. Finally, I changed the color of the line to read so the contrast can be easily seen.

Here is the result.


Now we can see alcoholic beverages account for much more of the overall ticket amount as the day goes one. By adding slicers for thinks like day of the week we do further analysis such as seeing if Fridays are the biggest percentage and so on.

Wrap Up

This illustration shows one of the most common scenarios to overlay charts. Using a percentage to see a trend related to the underlying values shown in the bar chart helps understand correlation. If you use slicers on your dashboard, make sure that you connect them to both charts (if you want them both filtered by the slicer of course).

Excel Tip #24: Removing “Buttons” from Excel Charts

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Annoyed by the Buttons, Remove Them

This is one of my shortest tips, but many users who are new to Excel charts look to remove the field buttons on the charts. See the image below to see what we are talking about.


It is really simple to hide them or turn them off. You can manage the field buttons on the ANALYZE tab on the POWERCHART TOOLS. If you want to turn all the buttons off, you only need to click the Field Buttons button which will look “unselected” and all the buttons are gone. If you want to remove only some of the buttons, expand the selection on the button and select or deselect what you want to see. (Although I am not a fan of buttons in dashboards because they are ugly.)


Here is the same chart without buttons. Be sure when you are designing your dashboards, you turn the buttons off so you can see how the chart looks for deployment. Buttons take up a lot of real estate in the chart. When they are removed your chart could look very different.


Chart Filter Buttons

One use case that I want to highlight is using filters on chart. When you add a filter for your chart, you need to use a field button to interact with the filter. The nice part about hiding the buttons and deploying to Excel Services, you can apply an underlying, hidden filter. However, if you want to change the filtered option you need to unhide the field buttons. If you want users to apply filters, you should use slicers not the filter option. Slicers are a much better user experience.