Adding Top 10 Charts to Power View Which Honor Filters

Before I get too far in the weeds, I have to recognize Rob Collie, his blog (http://www.powerpivotpro.com) and his book DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX. His book has been a great resource as I work through fun with DAX. I used information from his post on Dynamic TopN Reports via Slicers, Part 2 to solve my problem which I will describe next.  Thanks for the info Rob!


The problem I was trying to solve was creating a top 10 chart in Power View that showed the top 10 in context of what was filtered in the Power View report. If you click a pie slice in a chart, filter the top 10 at the same time. Also have it work with slicers. I was working with Power View and a couple other BI tools to see if this was possible. All tools are able to handle creating a calculation that will show the top 10 for a set of criteria, however, when the data was dynamically filtered on the page, only top 10 values that from the original measure that also worked in the filter worked.  So, now my top 10 charts would have fewer than 10 results.

I will walk through the process so you can see both ways and how I got to the eventual solution.

Both solutions use the RANKX function in Power Pivot. So let’s start their. I am using my Poll solution from the My Vote application built for Modern Apps Live. We have a Poll Response Count calculated measure:

Poll Response Count:=COUNTA([PollResponseID])

Using that as the starting measure we add the ability to get the top 10 responses by Poll. On the Poll table, which is joined to the Poll Response table by Poll ID, I added the following calculation to give rank values to the Polls.

Poll Response Rank:=RANKX(ALL(‘Poll’),[Poll Response Count])

This applies a rank value in ascending order to the Polls by Response Count.  Let’s apply this to a Power View report. In my report, I will be adding a Slicer on Category Name (all Polls belong to a Category), a bar chart with Response Count by Poll Question, a Pie Chart with Response Count by Category, and a table which shows the raw rank value for each question. Here is what that looks like:

image

In order to get the top responses in the bar chart we need to add Poll Response Rank to the Chart Filter (Not the view filter) for the bar chart. As you can see below, we pick the responses less than or equal to ten.

image

This applies the top 10 filter only to the chart which is what we want. However, when I filter by Off-Topic only two values remain in the chart. When you look at the table values ranked less than 10 overall will not be included in the chart. In some cases this is exactly the functionality you want. Here is what the filtered view looks like, only two of the 4 show up in the bar chart.

image

What if I wanted the slicer to also apply to the bar chart. We need to use a slightly different formula.

Poll Response Filtered Rank:=RANKX(ALLSELECTED(‘Poll’),[Poll Response Count])

By changing the chart filter to the new rank value you will see we get the filter applied based on the selections in the sheet.

image

How Does It Work?

The key is what I learned from Rob’s writings and samples. The ALL() function removes external context when applying a function. So ALL in this case will return the full set of Polls and then apply the RANKX function. So, it is returning the rank for all polls. ALLSELECTED() applies the RANKX function to the currently selected values. So any operation within a Power View sheet which changes the filter context, such as a slicer, will reset the Polls to only the set of Polls which have been selected and then apply the ranking.

If you do not use ALL or ALLSELECTED, the context is the row and will always return 1. Which is not helpful.

Can We Reverse the Order?

If you need to show the bottom ranking, you can add to the RANKX function. RANKX syntax:

RANKX(<table>,<expression>[,<value> [,<order>] [, <ties>]]])

Value, Order, and Ties are optional parameters. The one that matters is the Order parameter. The Order can be 0 or 1 and it defaults to 0. 0 ranks the values in descending order. To get the bottom result, you would use 1 here. The following code shows how we would rank the bottom polls.

Poll Response Filtered Rank:=RANKX(ALLSELECTED(‘Poll’),[Poll Response Count],,1)

For the optional parameters, you only need to leave a placeholder if you do not need it. Why create a separate value for reverse order? Because Power View can easily handle less than 10, but not the last 10. By making the change above and not changing our standard rank you can see that the data is now filtering for the bottom ten based on how the ranking value is assigned.

image

I hope this helps you create even more effective visualizations with just a bit more DAX under your belt.

Exploring Excel 2013 for BI Tip #10: The Data Bar

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.  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!

Using the Data Bar

This feature has been a part of Excel for a long time.  However, as with any tool, some of the oldies are really good.  As a BI architect who worked with SQL Server tools, I am always amazed at what has been around in Excel for years.  So as part of this series, I will also highlight some important visualizations that have been around.

What is the Data Bar?

The data bar is a conditional formatting feature that can be applied to cells in Excel.  Data bars “fill” the cell proportionally based on the data that the formatting is applied to.  Data bars work with pivot table and standard data in Excel.  Our focus will be on using the data bar with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image

image

As you can see Data Bars are one option under Conditional Formatting.  Look for future tips to come on some of the other Conditional formatting options.

Adding Data Bars

The following sample is from the MyVote data generated from the Modern Apps Live! project.  In this sample, I have a simple pivot table which shows the Age range and number of poll submissions as shown below.

image

To add data bars highlight the area to add the bars and choose a format.

image

Advanced Settings

By clicking More Rules … you will be able to apply advanced options.

image

In my opinion, the most important setting to use here is at the top.  When working with pivot tables, you should at least choose Apply Rule to “All cells showing {field name} values.” By choosing this option, if new row values are added, the data bars will be appropriately applied. This also is necessary for filters and slicers to work correctly.

However, this option will also highlight the any total columns (in our case Grand Total was included).  If you only want counts with the row labels, you would choose “All cells showing “{field name}” values for “{field name}”.   You will find that this is the most common option to select when using data bars for data visualization.

Modifying Existing Data Bars

Once it has been added, you can modify the data bar choosing the Manage Rules option in the Conditional Formatting drop down.  This will open a dialog box which has the formatting rules for the selected pivot table.  There is a drop down, which allows you to select the rules for the sheet or other parts of the workbook.  From here you can see all of the rules applied and can edit the rule, create a new rule, delete a rule, or reorder the rules.

Data bars are a simple, but effective data visualization when you need to highlight the variance between values.  With the ability to apply the data bar to a field in a pivot table, it becomes a flexible visualization as well with very little effort involved.